I have a list of macro variables as shown below:
%let a=40;
%let b=55;
%let c=25;
%let d=38;
%let e = 41 and so on.
I would like to convert this to a sas dataset in the manner of
a b c d e
40 55 25 38 41
I could not find anything on a quick way to do this. All I can think of is writing a data step with datalines to create the dataset. Is there a quicker way to do it, given I have large number of variables? Thank you!
Are all your values numeric?
Can you use all existing macro variables without omitting any?
You should be able to do something like this, but I can't test this now so I can't work out the final details until tomorrow:
proc sql;
select name || '=' || value into : varlist separated by ';' from dictionary.macros
where scope ne 'AUTOMATIC';
quit;
data want;
&varlist;
run;
There are a few details to work out ... SCOPE is probably the wrong variable name for the WHERE clause ... in fact all the names from dictionary.macros need to be checked To patch the code, you will need to look at the structure of dictionary.macros.
***************** EDITED:
The original program wasn't too far off. Here's a modified version that should work a little better:
proc sql;
select catx('=', name, value) into : varlist separated by ';' from dictionary.macros
where (scope ne 'AUTOMATIC') and (substr(name, 1, 3) not in ('SQL', 'SYS') );
quit;
It generates assignment statements for all your macro variables, however ...
It works properly for numeric variables only, since it generates statements like a=5; It won't add quotes around the "5" unless you get fancier with the code.
%let a=40;
%let b=55;
%let c=25;
%let d=38;
%let e = 41;
data w;
a=&a;
b=&b;
c=&c;
d=&d;
e=&e;
run;
Are all your values numeric?
Can you use all existing macro variables without omitting any?
You should be able to do something like this, but I can't test this now so I can't work out the final details until tomorrow:
proc sql;
select name || '=' || value into : varlist separated by ';' from dictionary.macros
where scope ne 'AUTOMATIC';
quit;
data want;
&varlist;
run;
There are a few details to work out ... SCOPE is probably the wrong variable name for the WHERE clause ... in fact all the names from dictionary.macros need to be checked To patch the code, you will need to look at the structure of dictionary.macros.
***************** EDITED:
The original program wasn't too far off. Here's a modified version that should work a little better:
proc sql;
select catx('=', name, value) into : varlist separated by ';' from dictionary.macros
where (scope ne 'AUTOMATIC') and (substr(name, 1, 3) not in ('SQL', 'SYS') );
quit;
It generates assignment statements for all your macro variables, however ...
It works properly for numeric variables only, since it generates statements like a=5; It won't add quotes around the "5" unless you get fancier with the code.
From where does this arbitrary heap of macro variables originate? You might alleviate your problems by storing the values in a dataset from the start.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.