Get values of Macro Variables in a SAS Table

Question

I have a set of input macro variables in SAS. They are dynamic and generated based on the user selection in a sas stored process.

For example:There are 10 input values 1 to 10.
The name of the macro variable is VAR_. If a user selects 2,5,7 then 4 macro variables are created. 
&VAR_0=3;
&VAR_=2;
&VAR_1=5;
&VAR_2=7;

The first one with suffix 0 provides the count. The next 3 provides the values.

Note:If a user select only one value then only one macro variable is created. For example If a user selects 9 then &var_=9; will be created. There will not be any count macro variable. I am trying to create a sas table using these variables.

It should be like this

OBS    VAR
-----------
1      2
2      5
3      7
-----------

This is what I tried. Not sure if this is the right way to do approach it. It doesn't give me a final solution but I can atleast get the name of the macro variables in a table. How can I get their values ?

data tbl1;
do I=1 to &var_0;
VAR=CAT('&VAR_',I-1);
OUTPUT;
END;
RUN;
PROC SQL;
CREATE TABLE TBL2 AS 
SELECT I,
CASE WHEN VAR= '&VAR_0' THEN '&VAR_' ELSE VAR END AS VAR
from TBL1;
QUIT;

Thank You for your help.

Jay

score 1 · Answer 1 · answered Feb 23 '16 at 16:15

1

SAS helpfully stores them in a table for you already, you just need to parse out the ones you want. The table is called SASHELP.VMACRO or DICTIONARY.MACROS

Here's an example:

%let var=1;
%let var2=3;
%let var4=5;

proc sql;
create table want as
select * from sashelp.vmacro
where name like 'VAR%';
quit;

proc print data=want;
run;

answered Feb 23 '16 at 16:15

Reeza

20,510
4
21
38

Thank You Reeza. Worked fine. Appreciate your help. – learnlearn10 Feb 23 '16 at 16:37

Tom · Answer 2 · 2016-02-23T17:30:47.787

I think the real issue is the inconsistent behavior of the stored process. It only creates the 0 and 1 variable when there are multiple selections. I think that your example is a little off. If the value of VAR_0 is three then their should be a VAR_3 macro variable. Also the value of VAR_ and VAR_1 should be set to the same thing.

To fix this in the past I have done something like this. First let's assign the parameter name a macro variable so that the code is reusable for other programs.

%let name=VAR_;

Then first make sure the minimal macro variables exist.

%global &name &name.0 &name.1 ;

Then make sure that you have a count by setting the 0 variable to 1 when it is empty.

%let &name.0 = %scan(&&&name.0 1,1);

Then make sure that you have a 1 variable. Since it should have the same value as the macro variable without a suffix just re-assign it.

%let &name.1 = &&&name ;

Now your data step is easier.

data want ;
  length var $32 value $200 ;
  do i=1 to &&&name.0 ;
    var=cats(symget('name'),i);
    value=symget(var);
    output;
  end;
run;

score 0 · Accepted Answer · answered Feb 23 '16 at 16:18

0

I don't understand your numbering scheme and recommend changing it, if you can; the &var_ variable is very confusing.

Anyway, the easiest way to do this is SYMGET. That returns a value from the macro symbol table which you can specify at runtime.

%let VAR_0=3;
%let VAR_=2;
%let VAR_1=5;
%let VAR_2=7;

data want;  
  do obs = 1 to &var_0.;
    var = input(symget(cats('VAR_',ifc(obs=1,'',put(obs-1,2.)))),2.);
    output;
  end;
run;

answered Feb 23 '16 at 16:18

Joe

62,789
6
49
67

The variable pattern looks like how the web interface from SAS returns user selections. I have also seen where it just returns the one variable and does not create the `_0` variable at all. – Tom Feb 23 '16 at 16:30
@Tom Hmm, that's very odd then. Why would the 'first' be `&var_` and not `&var_1`?? – Joe Feb 23 '16 at 16:32
Not sure, I assumed the logic went like this. If you define the field as MONTH and it is not multi-select then get a variable named MONTH. So they wanted to also return a variable name MONTH when it is multi-select. I haven't looked at it in years, but I thought that it made both the MONTH and MONTH1. But perhaps it was my code that added the MONTH1 since it seemed so strange. – Tom Feb 23 '16 at 16:36
Thank You Joe. Worked fine. Appreciate your help. Tom is correct. The naming convention is a little different. The first variable will be under both VAR_ and VAR_1. If there is only one value selected then there will be only one variable i.e. VAR_. – learnlearn10 Feb 23 '16 at 16:41
If that's the case (if VAR_1 = VAR) then you won't need the I-1 bit or the IFC bit. – Joe Feb 23 '16 at 17:22

Get values of Macro Variables in a SAS Table

3 Answers3