Proc sql and macro variables

Question

I am trying to run a code that should work on tables created considering different factors. As these factors can be more than 1, I decided to create a macro %let to list them:

%let list= factor1 factor2 ...;

What I would like to do is run a code to create these tables using different factors. For each factor, I computed using proc means the mean and the standard deviation, so I should have the variables &list._mean and &list._stddev in the table created by the proc means for each factor. This table is labelled as t2 and I need to join to another table, t1. From t1 I am considering all the variables.

My main difficulties are, therefore, in the proc sql:

proc sql;
create table new_table as
select t1.*
, t2.&list._mean as mean
, t2.&list._stddev as stddev
from table1 as t1
left join table2 as t2
on t1.time=t2.time
order by t2.&list.
quit;

This code is returning an error and I think because I am considering t2.factor1 factor2, i.e. t2 is only applied to the first factor, not to the second one. What I would expect is the following:

  proc sql;
    create table new_table as
    select t1.*
    , t2.factor1._mean as mean
    , t2.factor1._stddev as stddev
    from table1 as t1
    left join table2 as t2
    on t1.time=t2.time
    order by t2.factor1.
    quit;

and another one for factor2. UPDATE CODE:

%macro test_v1(
     _dtb
    ,_input
    ,_output
    ,_time
    ,_factor
);

data &_input.;
    set &_dtb..&_input.;
    keep &_col_period. &_factor.;
run;

proc sort data = work.&_input.
           out = &_input._1;
    by &_factor. &_time.;
run;

%put ERROR: 2

proc means data=&_input._1 nonobs mean stddev;
    class &_time.;
    var &_factor.;
    output out=&_input._n (drop=_TYPE_) mean= stddev= /autoname ;
run;

%put ERROR: 3

proc sql;
create table work.&_input._data as 
select t1.*
        ,t2.&_factor._mean as mean
        ,t2.&_factor._stddev as stddev
from &_input. as t1
left join &_input._n as t2
on t1.&_time.=t2.&_time.
order by &_factor.;
quit;

%mend test_v1;

Then my question is on how I can consider multiple factors, defined into a macro as a list, as columns of tables and as input data into a macro (for example: %test(dataset, tablename, list).

Even if you fix your syntax error your plan cannot work. You cannot rename both FACTOR1_MEAN and FACTOR2_MEAN to the same name. — Tom
– Tom, Commented Nov 27, 2019 at 1:14
i think you can use one step (proc sort) instead of two steps(data+proc sort):proc sort data = have1(keep=time factor1) out = have3_1; by factor1 time; run; instead of data data have2; set have1; keep time factor1; run; proc sort data = have2 out = have2_1; by factor1 time; run; — Llex
– Llex, Commented Nov 27, 2019 at 9:50
Why does your macro reference a totally undefined macro variable, _col_period? Why do you sort by the "factor" variables instead TIME? Are you using the FACTOR variables as analysis variables or grouping variables? — Tom
– Tom, Commented Nov 27, 2019 at 13:38

Tom · Accepted Answer · 2019-11-27 13:55:10Z

2

I suspect that trying to use PROC SQL is what is making the problem hard. If you stick to just using normal SAS syntax your space delimited list of variable names is easy to use.

So taking your code and tweaking it a little:

%macro test_v1
(_dtb    /* Input libref */
,_input  /* Input member name */
,_output /* Output dataset */
,_time   /* Class/By variable(s) */
,_factor /* Analysis variable(s) */
);

proc sort data= &_dtb..&_input. out=_temp1;
  by &_time. ;
run;

proc means data=_temp1 nonobs mean stddev;
  by &_time.;
  var &_factor.;
  output out=_temp2 (drop=_TYPE_) mean= stddev= /autoname ;
run;

data &_output. ;
  merge _temp1 _temp2 ;
  by &_time.;
run;

%mend test_v1;

We can then test it using SASHELP.CLASS by using SEX as the "time" variable and HEIGHT and WEIGHT as the analysis variables.

%test_v1(_dtb=sashelp,_input=class,_output=want,_time=sex,_factor=height weight);

edited Nov 27, 2019 at 13:55

answered Nov 27, 2019 at 1:21

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

still_learning Over a year ago

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. This is the error the I get. The issue with proc sql is that I am applying in this way t2. only to the first factor, factor1, so I have t2.factor1 factor2 and this is wrong, as I should have: t2.factor1, t2.factor2

Richard Over a year ago

@a.rimbaud As Tom shows Proc MEANS has a large feature set for you to use. Instead of coding a macro that simply replaces args into statements learning the Procs statements and coding without a macro means there is less to support long-term and makes life easier for the coder or code inheritor.

Llex · Accepted Answer · 2019-11-27 09:43:02Z

1

You can try to add macro loop to your macros by scanning list of factors. It could look like:

%macro test(list);
   %do i=1 to %sysfunc(countw(&list,%str( )));
      %let factorname=%scan(&list,&i,%str( ));
       /* if macro variable list equals factor1 factor2 then there would be
       two iterations in loop, i=1 factorname=factor1 and i=2 factorname=2*/
      /*your code here*/
   %end
%mend test;

UPDATE:

%macro test(_input, _output, factors_list); %macro d; %mend d;
%do i=1 %to %sysfunc(countw(&factors_list,%str( )));
%let tfactor=%scan(&factors_list,&i,%str( ));
proc sort data = work.&_input.
           out = &_input._1;
    by &factors_list. time;
run;

proc means data=&_input._1 nonobs mean stddev;
    class time;
    var &tfactor.;
    output out=&_input._num (drop=_TYPE_) mean= stddev= /autoname ;
run;

proc sql;
   create table &_output._&tfactor as
   select t1.*
   , t2.&tfactor._mean as mean
   , t2.&tfactor._stddev as stddev
   from &_input as t1
   left join &_input._num as t2
   on t1.time=t2.time
   order by t1.&tfactor;
quit;

%end;
%mend test;

%test(have,newdata,factor1 factor2);

Have dataset:

+------+---------+---------+
| time | factor1 | factor2 |
+------+---------+---------+
|    1 |   12345 |    1234 |
|    2 |     123 |      12 |
|    3 |       1 |      -1 |
|    4 |     -12 |    -123 |
|    5 |   -1234 |  -12345 |
|    6 |    9876 |     987 |
|    7 |      98 |       8 |
|    8 |       9 |       7 |
|    1 |    1234 |     123 |
|    2 |      12 |       1 |
|    3 |      12 |     -12 |
|    4 |    -123 |   -1234 |
|    5 |  -12345 | -123456 |
|    6 |     987 |      98 |
|    7 |       9 |      -9 |
|    8 |    1234 |    1234 |
+------+---------+---------+

NEWDATA_FACTOR1:

+------+---------+---------+---------+--------------+
| time | factor1 | factor2 |  mean   |    stddev    |
+------+---------+---------+---------+--------------+
|    5 |  -12345 | -123456 | -6789.5 | 7856.6634458 |
|    5 |   -1234 |  -12345 | -6789.5 | 7856.6634458 |
|    4 |    -123 |   -1234 |   -67.5 | 78.488852712 |
|    4 |     -12 |    -123 |   -67.5 | 78.488852712 |
|    3 |       1 |      -1 |     6.5 | 7.7781745931 |
|    7 |       9 |      -9 |    53.5 | 62.932503526 |
|    8 |       9 |       7 |   621.5 | 866.20580695 |
|    3 |      12 |     -12 |     6.5 | 7.7781745931 |
|    2 |      12 |       1 |    67.5 | 78.488852712 |
|    7 |      98 |       8 |    53.5 | 62.932503526 |
|    2 |     123 |      12 |    67.5 | 78.488852712 |
|    6 |     987 |      98 |  5431.5 |  6285.472178 |
|    1 |    1234 |     123 |  6789.5 | 7856.6634458 |
|    8 |    1234 |    1234 |   621.5 | 866.20580695 |
|    6 |    9876 |     987 |  5431.5 |  6285.472178 |
|    1 |   12345 |    1234 |  6789.5 | 7856.6634458 |
+------+---------+---------+---------+--------------+

NEWDATA_FACTOR2:

+------+---------+---------+----------+--------------+
| time | factor1 | factor2 |   mean   |    stddev    |
+------+---------+---------+----------+--------------+
|    5 |  -12345 | -123456 | -67900.5 | 78567.341564 |
|    5 |   -1234 |  -12345 | -67900.5 | 78567.341564 |
|    4 |    -123 |   -1234 |   -678.5 |  785.5956339 |
|    4 |     -12 |    -123 |   -678.5 |  785.5956339 |
|    3 |      12 |     -12 |     -6.5 | 7.7781745931 |
|    7 |       9 |      -9 |     -0.5 |  12.02081528 |
|    3 |       1 |      -1 |     -6.5 | 7.7781745931 |
|    2 |      12 |       1 |      6.5 | 7.7781745931 |
|    8 |       9 |       7 |    620.5 | 867.62002052 |
|    7 |      98 |       8 |     -0.5 |  12.02081528 |
|    2 |     123 |      12 |      6.5 | 7.7781745931 |
|    6 |     987 |      98 |    542.5 | 628.61792847 |
|    1 |    1234 |     123 |    678.5 |  785.5956339 |
|    6 |    9876 |     987 |    542.5 | 628.61792847 |
|    1 |   12345 |    1234 |    678.5 |  785.5956339 |
|    8 |    1234 |    1234 |    620.5 | 867.62002052 |
+------+---------+---------+----------+--------------+

edited Nov 27, 2019 at 9:43

answered Nov 27, 2019 at 8:34

Llex

1,7681 gold badge14 silver badges27 bronze badges

12 Comments

still_learning Over a year ago

Thanks Alexey. %let list=factor1 factor2; I wrote in this way:

%macro test(list);     %do i=1 %to %sysfunc(countw(&list,%str()));       %let factorname=%scan(&list,&i,%str());       %macro_v1(dataset,table, time, &list);     %end %mend test;

but no output has been returned. Is it right how I have used list also in the macro_v1?

Llex Over a year ago

try this:

%macro test(list); %do i=1 %to %sysfunc(countw(&list,%str())); %let factorname=%scan(&list,&i,%str()); %macro_v1(dataset,table, time, &factorname); %end; %mend test;  %test(&list);

still_learning Over a year ago

it has given me the following error: "ERROR: The text expression &list contains a recursive reference to the macro variable list. The macro variable will be assigned the null value."

Llex Over a year ago

@a.rimbaud tfactor stores one factor from factor_list and in each iterations it changes. For example we have factor_list equals factor1 factor2 factor3, countw function counts num of word which split by space, then loop iterates by each word and tfactor contains the word in each iteration(on the first iteration tfactor contains first word of factor_list, which equals factor1, etc.)

still_learning Over a year ago

Thank you Alexey

|

Collectives™ on Stack Overflow

Proc sql and macro variables

2 Answers 2

2 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related