topic Extracting data from a single cell to store and use in a macro in SAS Procedures

Extracting data from a single cell to store and use in a macro

George3 — Mon, 07 Oct 2019 17:21:45 GMT

Hello all,

I am working on a piece of a process that will use the numerical values of two variables. I want to use these two values as information for a larger process that will occur in a macro. Each of the variables needs to be by themselves. Working through an example will help. Take the following data:

DATA ex3;
INPUT variable1 variable2;
CARDS;
5 7
;
RUN;

The value of variable1 is 5 and the value of variable2 is 7. I want to be able to have SAS extract 5 and have it represented as "c" then extract 7 and have it represented as "d". This way I can use the "c" and "d" as values in my macro as follows:

%macro call_bunch;
%local c d ;

model &c &d, ****** the model command is part of a different macro process ********;

%end;
%mend;
%call_bunch

Any suggestions on how I do this?

Re: Extracting data from a single cell to store and use in a macro

Tom — Mon, 07 Oct 2019 17:28:38 GMT

You can use CALL SYMPUTX() function to store the value into macro variables.

data _null_;
  set ex3;
  call symputx('c',variable1);
  call symputx('d',variable2);
run;

Note that since you made C and D local macro variables to the macro CALL_BUNCH you would need to run this step while CALL_BUNCH is running. So either as part of the macro or in a macro that is called by CALL_BUNCH.

Re: Extracting data from a single cell to store and use in a macro

George3 — Mon, 07 Oct 2019 17:42:36 GMT

Tom,

This looks great, but your note about this needs to be part of the macro or in a macro that is called has me a bit puzzled, not by its logic but by its structure. I am puzzled because the data for the larger macro "bunch" will obviously be different from the data for c and d.

Are you meaning the following:

%macro call_bunch;
%local c d ;

data _null_;
set ex3;

call symputx('c',variable1);
call symputx('d',variable2);
run;

model &c &d,

%end;
%mend;
%call_bunch;

Is this what are meaning? If this is the case, wouldn't the system stop because it wouldn't know which data to use.

Re: Extracting data from a single cell to store and use in a macro

Tom — Mon, 07 Oct 2019 17:50:08 GMT

Your macro as currently constructed doesn't make any sense. It generates a DATA step and then a free-floating MODEL statement. You cannot have that statement outside of a PROC step. Put you couldn't call the macro in the middle of PROC step since the start of the DAT step would end the definition of the PROC step.

Not sure why you have the macro at all.

data _null_;
  set ex3;
  call symputx('c',variable1);
  call symputx('d',variable2);
run;

proc glm data=xxx ;
  model &c &d, 
run;

You need to explain what your real problem is to get a real solution.

Re: Extracting data from a single cell to store and use in a macro

George3 — Mon, 07 Oct 2019 17:52:30 GMT

This does help. The macro is designed to perform the analysis and it is essential to this. I will try this and see what happens. Thank you for the help.

Re: Extracting data from a single cell to store and use in a macro

George3 — Mon, 07 Oct 2019 18:02:16 GMT

Tom,

This worked beautifully. I get it now! Thank you.