Subsetting a dataset using Macro

[Pujasharma]

Hi All,

I am having issue while subsetting a dataset using Macro.

here is my code :

%macro dt (value);

data raw_new;

set raw;

where index(score, "&value");

%dt(apple_1);

%dt(orange_2);

run;

From Raw data set I want to create raw_new data set. Variable score has 1000 values and I want to keep only those values which contains "apple_1" and "orange_2". Every time I run this code, I get only values with orange_2 in my dataset. apple_1 does not get appended to orange_2. Could you please suggest what I'm doing wrong.

Thank You!

[Ksharp]

where index(score, "&value");

--->

if index(score, "&value");

OR

where score contains "&value";

[Amir · Accepted solution]

Hi,

The macro starts ok, but if you always write to the same data set (raw_new) each time you invoke the macro function %dt then raw_new will be overwritten each time, the last time being with orange_2 data which might be why you're not seeing apple_1 data.

Try something like (untested):

%macro dt (value);

  data raw_new_temp;

    set raw;

    where index(score, "&value");

  run;

  proc append base=raw_new

              data=raw_new_temp

              force;

  run;

%mend dt;

%dt(apple_1);

%dt(orange_2);

The above code creates a temporary data set then appends it to raw_new.

Regards,

Amir.

Message was edited by: Amir - corrected reference to raw_new.

[Pujasharma]

That worked, Amit! Thank you!