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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

 

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20
%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;
Astounding
PROC Star

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.

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

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.

Discussion stats
  • 3 replies
  • 4347 views
  • 1 like
  • 4 in conversation