topic Re: Run a Macro by reading from another dataset in SAS Programming

Run a Macro by reading from another dataset

sas51 — Wed, 27 Apr 2022 07:26:43 GMT

Hi all,

I am trying to run correlation statistics on all the possible combinations of variables from my dataset. I have already created the possible combinations by joining dataset itself with distinct.

My code is

%MACRO CORR(VAR1, VAR2);
PROC CORR
DATA = HAVE
OUTP = WANT;
VAR VAR1; WITH VAR2; RUN;
%MEND;

I am trying to run the macro above by reading up on VAR1 and VAR2 from another dataset using SYSFUNC function.

Appreciate any assistance. Thanks in advance!

Re: Run a Macro by reading from another dataset

Kurt_Bremser — Wed, 27 Apr 2022 07:52:55 GMT

Write the code to a file, and include it:

filename progfile temp;

data _null_;
set have;
file progfile;
line = cats('%corr(',var1,',',var2,')');
put line;
run;

%include progfile;

filename progfile clear;

Re: Run a Macro by reading from another dataset

sas51 — Wed, 27 Apr 2022 08:22:23 GMT

Hi Kurt, sorry for the confusion, this is how my datasets are now:

Dataset: HAVE
Date Variable1 Variable2 Variable3 ... Variable10
31JAN2000 1.3 5.0 9.1 ... 4.3
29FEB2000 5.1 6.7 9.8 ... 5.1

Dataset: COMBINATION
Variable Variable_Paired
Variable1 Variable2
Variable1 Variable3
...
Variable10 Variable9

My combinations (COMBINATION) are in a different dataset as my dataset containing my variables (HAVE).

I am still very new to SYSFUNC are you suggesting something like %SYSFUNC(PROGFILE) in my macro?

Re: Run a Macro by reading from another dataset

Kurt_Bremser — Wed, 27 Apr 2022 09:00:59 GMT

So my code should be

filename progfile temp;

data _null_;
set combination;
file progfile;
line = cats('%corr(',variable,',',variable_paired,')');
put line;
run;

%include progfile;

filename progfile clear;

Misunderstandings like this are a main reason why we always ask to provide example data in readily usable form, which means as data steps with datalines.

We can then simply copy the code to our environment and submit it, and there's no questions at all about dataset names, raw content, variable types and other attributes.

So your first dataset should be posted as

data have;
input Date :date9. Variable1 Variable2 Variable3;
format date date9.;
datalines;
31JAN2000 1.3 5.0 9.1
29FEB2000 5.1 6.7 9.8
;

Personally, I prefer to use the YYMMDD10. format/informat for dates, as this is the international standard (ISO 8601)

Re: Run a Macro by reading from another dataset

Kurt_Bremser — Wed, 27 Apr 2022 09:05:59 GMT

PS SYSFUNC does not play any role here.

%SYSFUNC is a macro function which allows to use (most) data step functions while the macro preprocessor executes macro code.

What my code does (in detail):

it defines a temporary file reference (such a file will reside - invisible to you - in your WORK directory and vanish as soon as your SAS session terminates)
it then writes a series of macro calls to this file
then it includes the file, so the series of macro calls is executed like code you typed into your program
and it finally clears the file reference (in the manner of good house-keeping), causing the file to be deleted right there

Re: Run a Macro by reading from another dataset

average_joe — Thu, 28 Apr 2022 18:10:35 GMT

You can directly read the combinations data set and dynamically make the macro call with call execute.

UNTESTED

data _null_;
set combinations;
cmd = cats('%corr(', variable, ',', variable_paired, ');');
*put cmd=;  * confirm syntax;
call execute(cmd);
run;

Re: Run a Macro by reading from another dataset

Tom — Thu, 28 Apr 2022 18:44:56 GMT

Why do you need such complications?

proc corr data=have;
  var variable1-variable10 ;
run;

Re: Run a Macro by reading from another dataset

average_joe — Fri, 29 Apr 2022 11:58:27 GMT

There you go. Knowledge of the procs trumps all nifty programming tricks.