topic Re: Macro filter in SAS Programming

Macro filter

Alana13 — Mon, 20 Aug 2018 20:48:30 GMT

Hi,

I made this macro, that filter my data set (named A) by id and put it in a new data set (named B), then I run the proc means procedure in my filtered data set. It works fine (code below).

data A;
input x y z id;
datalines;
1 2 3 1
4 5 6 1
7 8 9 2
4 8 7 2
1 4 7 2
4 8 9 3
;
run;

%macro filter(id);

 data B_&id;
 set A;
 where id = &id;
 run;

 proc means data=B_&id noprint;
 var x y z;
 output out=new_&id(keep=_STAT_ x y z);
 run;

 %mend filter;

 %filter(2);

But I need something more, I need that it runs over a specific list of ids and run proc means iteratively.

Like, if I code %filter(1 8 9) it should give as a result these three filtered data sets (B_1 B_8 and B_9) and the three data sets with proc means result (new_1 new_8 and new_9).

It will be used for several others procs too, some of then not allow the by option, so it's not a possibility.

From previous search, I know that I should use the %do macro statement with %sysfunc, but I'm not sure how.

Can anyone share some ideas?

Thanks, Alana.

Macro filter

Alana13 — Mon, 20 Aug 2018 20:44:46 GMT

Hi,

I made this macro, that filter my data set (named A) by id and put it in a new data set (named B), then I run the proc means procedure in my filtered data set. It works fine (code below).

data A;
input x y z id;
datalines;
1 2 3 1
4 5 6 1
7 8 9 2
4 8 7 2
1 4 7 2
4 8 9 3
;
run;

%macro filter(id);

 data B_&id;
 set A;
 where id = &id;
 run;

 proc means data=B_&id noprint;
 var x y z;
 output out=new_&id(keep=_STAT_ x y z);
 run;

 %mend filter;

 %filter(2);

But I need something more, I need that it runs over a specific list of ids and run proc means iteratively.

Like, if I code %filter(1 8 9) it should give as a result these three filtered data sets (B_1 B_8 and B_9) and the three data sets with proc means resut (new_1 new_8 and new_9).

It will be used for several others procs too, some of then not allow the by option, so it's not a possibility.

From previous search, I know that I should use the %do macro statement with %sysfunc, but I'm not sure how.

Can anyone share some ideas?

Thanks, Alana.

Re: Macro filter

PaigeMiller — Mon, 20 Aug 2018 20:54:58 GMT

Use a BY statement in PROC MEANS. Do not use macros for this purpose.

proc sort data=a;
    by id;
run;

proc means data=a noprint;
    by id;
    var x y z;
    output out=new;
run;

If you only want IDs 1 8 and 9 in the analysis, then change the above to

proc means data=a(where=(id in (1,8,9))) noprint;

Re: Macro filter

Alana13 — Mon, 20 Aug 2018 20:55:36 GMT

Hi Paige,

Thanks for the answer!

But, I will use it for several others procs too, some of then do not allow the by statement, so it's not a possibility.

Regards,

Alana.

Re: Macro filter

PaigeMiller — Mon, 20 Aug 2018 20:58:02 GMT

Almost every SAS PROC that does data analysis has a BY statement (and DATA steps have BY statements too).

So which data analysis PROC are you looking at that does not allow a BY statement?

Re: Macro filter

Alana13 — Mon, 20 Aug 2018 20:59:26 GMT

PROC optmodel

Regards,

Alana.

Re: Macro filter

PaigeMiller — Mon, 20 Aug 2018 21:16:37 GMT

Ok, you make a good point.

Run the analyses prior to OPTMODEL with a BY statement. Then

%macro do_this(ids);
    %let num_ids=%sysfunc(countw(&ids));
    %do i=1 %to &num_ids;
         %let this_id=%scan(&ids,&i,%str( ));
         proc optmodel;
             read data whatever(where=(id=&this_id)) ...
                  /* Proc OPTMODEL Statements go here */
             create data id_&this_id from ... ;
         run;
    %end;
%mend;

%do_this(1 8 9)

Re: Macro filter

Alana13 — Tue, 21 Aug 2018 12:34:18 GMT

Thanks Paige!

It works!

Best regards,

Alana.