Into clause in sql sas

What is the question here?

What are you trying to do?

Do you want SQL to create a table or macro variables? It can't do both in the same query.

proc sql noprint; select varnum , name , case when type ='char' then cats('$',length) else cats(length) end into :dummy , :name_list separated by ' ' , :length_list separated by ' ' from dictionary.columns where libname = 'OFFLINE' and memname = %upcase("LIS002") order by varnum ; quit;
6 REPLIES 6 Barite | Level 11 Re: PROC SQL INTO Clause when CASE Posted 01-15-2019 10:24 PM (4330 views) | In reply to RajasekharReddy 
proc sql noprint; select NAME,length, case when upcase(type) ='CHAR' then '$' else ' ' end as var_type into: varlist separated by ' ' , : var_len separated by ',', : var_type separated by ' ' from dictionary.COLUMNS where upcase(libname) = 'SASHELP' and upcase(MEMNAME) = upcase("CLASS") ; quit; %put &=var_type &=var_len &=varlist;
Super User Re: PROC SQL INTO Clause when CASE Posted 01-15-2019 10:39 PM (4321 views) | In reply to RajasekharReddy

What is the question here?

What are you trying to do?

Do you want SQL to create a table or macro variables? It can't do both in the same query.

proc sql noprint; select varnum , name , case when type ='char' then cats('$',length) else cats(length) end into :dummy , :name_list separated by ' ' , :length_list separated by ' ' from dictionary.columns where libname = 'OFFLINE' and memname = %upcase("LIS002") order by varnum ; quit;
Fluorite | Level 6 Re: PROC SQL INTO Clause when CASE Posted 01-16-2019 12:09 AM (4305 views) | In reply to Tom

Thank you very much for solution .

I am trying to get result as pet below to pass the same values in LENGTH statement .

i have tried below code , however for NUM variable how i cam get space like " NUM 8. "

 proc sql noprint; create table tt as select NAME,length, case when upcase(type) ='CHAR' then '$' else '.' end as var_type into: varlist separated by ' ' , : var_len separated by ',', : var_type separated by ' ' from dictionary.COLUMNS where upcase(libname) = 'SASHELP' and upcase(MEMNAME) = upcase("CLASS") ; select case when var_type='$' then cats('',name,var_type,length,'.') when var_type='.' then cats(' ',name,length,var_type) else ' ' end as var_all into:var_all separated by ' ' from tt ; quit; %put &=var_all;

%put &=var_all;
VAR_ALL=Name$8. Sex$1. Age8. Height8. Weight8.

But i want numeric variable length as "Age 8. Height 8. Weight 8. "

Fluorite | Level 6 Re: PROC SQL INTO Clause when CASE Posted 01-16-2019 12:32 AM (4292 views) | In reply to Tom

Thank you very much for solution .

I am trying to get result for macro variable as per below to pass the same values in LENGTH statement .

%put &=var_all;
VAR_ALL= Name$8. Sex$1. Age 8. Height 8. Weight 8.

i have tried below code , however for NUM variable how i can get space like " NUM 8. "

 proc sql noprint; create table tt as select NAME,length, case when upcase(type) ='CHAR' then '$' else '.' end as var_type into: varlist separated by ' ' , : var_len separated by ',', : var_type separated by ' ' from dictionary.COLUMNS where upcase(libname) = 'SASHELP' and upcase(MEMNAME) = upcase("CLASS") ; select case when var_type='$' then cats('',name,var_type,length,'.') when var_type='.' then cats(' ',name,length,var_type) else ' ' end as var_all into:var_all separated by ' ' from tt ; quit; %put &=var_all;

%put &=var_all;
VAR_ALL=Name$8. Sex$1. Age8. Height8. Weight8.

But i want numeric variable length as "Age 8. Height 8. Weight 8. "