topic Re: PROC SQL INTO Clause when CASE in SAS Programming

PROC SQL INTO Clause when CASE

RajasekharReddy — Wed, 16 Jan 2019 03:12:16 GMT

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;

Re: PROC SQL INTO Clause when CASE

r_behata — Wed, 16 Jan 2019 03:24:26 GMT

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;

Re: PROC SQL INTO Clause when CASE

Tom — Wed, 16 Jan 2019 03:39:00 GMT

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;

Re: PROC SQL INTO Clause when CASE

34reqrwe — Wed, 16 Jan 2019 03:42:49 GMT

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

Re: PROC SQL INTO Clause when CASE

RajasekharReddy — Wed, 16 Jan 2019 05:09:20 GMT

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

Re: PROC SQL INTO Clause when CASE

RajasekharReddy — Wed, 16 Jan 2019 05:32:11 GMT

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

Re: PROC SQL INTO Clause when CASE

Tom — Wed, 16 Jan 2019 14:58:14 GMT

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)