Get Data from SAS table to SAS Macro Variable

Question

I want to create a macro that reads a value from a SAS table and stores that value in a Global variable.

The purpose is to use this value in SAS DIS JOBs.

I have tested following:

%GLOBAL &myMVar.;
%Macro Get_data(myDataset,myLine,myColumn,myMVar);
data _null_;
set &myDataset.;
if _N_ = &myLine. 
    then do;
        call symputx(symget('myMVar'),&myColumn.);
    end;
run;
%MEND Get_data;

*Calling a Macro program;
LIBNAME dtvault BASE "/sasdata/DataVault";
%Get_data(dtvault.codes,1,cod,myMVar);
%put &myMVar;
run;

**I do not have any results. The log: **

*Calling a Macro program;
38         LIBNAME dtvault BASE "/sasdata/DataVault";
NOTE: Libref DTVAULT was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: /sasdata/DataVault
39         %Get_data(dtvault.codes,1,cod,myMVar);

NOTE: There were 2 observations read from the data set DTVAULT.CODES.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


40         %put &myMVar;

41         run;
42         
43         GOPTIONS NOACCESSIBLE;
44         %LET _CLIENTTASKLABEL=;
45         %LET _CLIENTPROCESSFLOWNAME=;
46         %LET _CLIENTPROJECTPATH=;
2                                                          The SAS System                          10:06 Wednesday, October 25, 2017

47         %LET _CLIENTPROJECTPATHHOST=;
48         %LET _CLIENTPROJECTNAME=;
49         %LET _SASPROGRAMFILE=;
50         %LET _SASPROGRAMFILEHOST=;
51         
52         ;*';*";*/;quit;run;
53         ODS _ALL_ CLOSE;
54         
55         
56         QUIT; RUN;
57    

Thanks

Answer 1

The way you are initializing the global macro variable if what you want is a global macro variable called myVar. The %global statement does not require the ampersand.

Also, if you're going to instantiate that macro variable outisde of your macro definition anyway, then there's no need to specify it as a parameter of you macro definition:

%GLOBAL myMVar.;
%Macro Get_data(myDataset,myLine,myColumn);
data _null_;
set &myDataset.;
if _N_ = &myLine. 
    then do;
        call symputx('myMVar',&myColumn.);
    end;
run;
%MEND Get_data;

*Calling a Macro program;
LIBNAME dtvault BASE "/sasdata/DataVault";
%Get_data(dtvault.codes,1,cod);
%put &myMVar;

Note that the symputx call routine allows for specifying the symbol table in which you want your variable created. You can then do away with the %global statement:

%Macro Get_data(myDataset,myLine,myColumn);
data _null_;
set &myDataset.;
if _N_ = &myLine. 
    then do;
        call symputx('myMVar',&myColumn.,'G');
    end;
run;
%MEND Get_data;

*Calling a Macro program;
LIBNAME dtvault BASE "/sasdata/DataVault";
%Get_data(dtvault.codes,1,cod);
%put &myMVar;

EDIT: in answer to your comment, I used the above code on a test dataset:

libname dtvault (work);

data dtvault.codes;
cod='test';
run;

%Macro Get_data(myDataset,myLine,myColumn);
data _null_;
set &myDataset.;
if _N_ = &myLine. 
    then do;
        call symputx('myMVar',&myColumn.,'G');
    end;
run;
%MEND Get_data;

%Get_data(dtvault.codes,1,cod);
%put &myMVar;

The log produces this output:

42         %Get_data(dtvault.codes,1,cod);

NOTE: There were 1 observations read from the data set DTVAULT.CODES.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
2                                                          The SAS System                          10:04 Wednesday, October 25, 2017

      cpu time            0.00 seconds


43         %put &myMVar;
test

If the %put statement, on your data, produces nothing; could it be that the value for of cod on line 1 of your dataset is blank?

Answer 2

I see two main mistakes that will make your program not work.

The & is not part of the macro variable name. It is the trigger that lets the macro processor know that you want it to replace the macro variable reference with the value of the macro variable referenced. So if you want to define a macro variable named mymvar your %global statement needs to look like this.

%global mymvar;

You are also getting impacted by the scoping of macro variables. If your program starts with these lines

%global mymvar;
%macro get_data(mydataset,myline,mycolumn,mymvar);

then you have defined a LOCAL macro variable mymvar that will hide the global macro variable mymvar while the macro get_data is running. So if you set mymvar to some value it will disappear when the macro finishes running.

Since you want your macro to generate a macro variable that will be available after the macro finishes running the you will want to make sure that the macro variable that it creates either already exists or is created in the GLOBAL macro space.

For added flexibility you might want to pass the NAME of the macro variable that you want the macro to use as a parameter to the macro. You could then use the %SYMEXIST() function to decide if you need to create a global macro variable or not. Notice that now we do want the & in the %global statement since we want to create a macro variable whose name is the value of another macro variable.

%macro get_data(dataset,line,column,mvar);
%if not %symexist(&mvar) %then %global &mvar ;
data _null_;
  set &dataset ;
  if _n_=&line then do;
    call symputx("&mvar",&column);
    stop;
  end;
run;
%mend get_data;

Now when you call the macro you pass in the values that you want for the parameters. Note that one nice feature of SAS macros is that even for parameters that are defined as positional arguments you can use named parameters in the macro call.

%get_data(dataset=myDataset,line=1,column=myVar,mvar=myMvar)
%put &=myMvar;