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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: