PROC SQL INTO Clause when CASE

[RajasekharReddy]

Hello,

Please help me tog generate macro variable values as

Varlist as variable names,

var_type as variable type (char= $ num='')

length as variable length

proc sql noprint;
	create table  test as
       select NAME,length,
			case when upcase(type) ='CHAR' then '$' 
			     when upcase(type) ='NUM' then ''
			else '' 
			end as var_type 
			into: varlist separated by ' ' ,
	                    : var_len separated by ',',
		            : var_type separated by ' ' from dictionary.COLUMNS 
           	      where upcase(libname) = 'OFFLINE' and upcase(MEMNAME) = upcase("LIS002") 
	   order by varnum  ;
     quit;

[r_behata]

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;
 
  

[Tom · Accepted solution]

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;

[RajasekharReddy]

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;

In log shows

 %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. "

[RajasekharReddy]

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;

In log shows

 %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. "

[RajasekharReddy]

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;

In log shows

 %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. "

[Tom]

You don't want the periods if you are trying to generate values for a LENGTH statement.  Lengths can only be integers so there is no need for a decimal place.

Use the CATX() function to place a delimiter between values.

...
catx(' ',name,case when type='char' then '$' end,length)
...
into :varlist separated by ' '

That will generate

NAME $ 10 AGE 8 

Or you could get rid for the space after the $ 

catx(' ',name,case when type='char' then cats('$',length) else cats(length) end)

[34reqrwe]

Remove the create table test as line  - that is causing the INTO to be ignored