Run a Macro by reading from another dataset

[sas51]

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!

[Kurt_Bremser]

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;

[sas51]

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?

[Kurt_Bremser · Accepted solution]

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)

[Kurt_Bremser]

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

[average_joe]

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;

[Tom]

Why do you need such complications?

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

[average_joe]

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