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;
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;
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;
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;
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. "
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. "
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)
Remove the create table test as line - that is causing the INTO to be ignored
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!
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.
Ready to level-up your skills? Choose your own adventure.