BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RajasekharReddy
Fluorite | Level 6

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

6 REPLIES 6
r_behata
Barite | Level 11
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
Super User Tom
Super User

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Super User Tom
Super User

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
Quartz | Level 8

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 5467 views
  • 3 likes
  • 4 in conversation