Macro filter

[Alana13]

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.

[Alana13]

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.

[PaigeMiller]

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;

[Alana13]

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.

[PaigeMiller]

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?

[Alana13]

PROC optmodel

Regards,

Alana.

[PaigeMiller · Accepted solution]

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)

[Alana13]

Thanks Paige!

It works!

Best regards,

Alana.