Macro

[RedBishop]

Dear community,

I have a short question that hopefully someone knows the answer to.

Suppose you'd wish to simply extract 5 datasets from 1 dataset, using a macro:

%macro MyMacro;
%do i = 1 %to 5; 
     CurrentAnimal = ('dog', 'cat', 'sheep', 'cow', 'horse') (i)

     proc sql;
     create table Want_ & i as
     select *
     from Have
     where Animal = CurrentAnimal;
     quit;

%end;
%mend MyMacro;

%MyMacro

It gives no errors, but no output either.

Anyone knows what's going on and how to fix it?

Thank you in advance!

[novinosrin]

HI @RedBishop  Are you after this by any chance?

%macro MyMacro;
 %let   CurrentAnimal = 'dog', 'cat', 'sheep', 'cow', 'horse';
%do i = 1 %to 5; 
   

     proc sql;
     create table Want_&i as
     select *
     from Have     
         where Animal in (&CurrentAnimal);
     quit;

%end;
%mend MyMacro;
%mymacro  

[JerryV]

Since I don't have your data I used sashelp.cars.  How about this?

%macro MyMacro;
%let CurrentAnimal =Audi, BMW; 
%let dim=%sysfunc(countw(%quote(&CurrentAnimal.),%str(,)));
%do i = 1 %to &dim;
  %let this=%scan(%quote(&CurrentAnimal.),&i,%str(,));
     proc sql;
     create table Want_&this. as
     select *
     from sashelp.cars where make = "&this.";
     quit;
%end;
%mend MyMacro;
%MyMacro

[RedBishop]

@novinosrin , almost! This stores 5 copies of the same dataset.
I was looking for one per animal.

[novinosrin]

Ah ok, it appears others have read the question correctly and have posted the needed solution

[PaigeMiller]

Usually not a good idea to chop up a large data set into five (or any other number) of subsets. Whatever analysis you were going to do can be done with the one big data set and a BY statement, which simplifies your programming considerably, compared to programming an analysis to do the same thing over five data sets.

[ballardw · Accepted solution]

Or maybe

%macro MyMacro;
%let CurrentAnimal = dog cat sheep cow horse;
%do i = 1 %to %sysfunc(countw,&CurrentAnimal.); 
	  %let animal = %scan(&CurrentAnimal.,&i.);
     proc sql;
     create table Want_&i as
     select *
     from Have     where Animal = "&Animal.";
     quit;

%end;
%mend MyMacro;

%MyMacro

Your posted code has a large number of errors. First your currentanimal assignment statement is 1) not terminated and 2) out side of a data step and 3) has a very mysterious (i) at the end that would not be acceptable in any syntax for SAS. I have assumed that you want the macro to process the values and placed the value into a Macro variable using the %let statment.

Second in your Proc Sql you have multiple spaces in what appears to be the name you want the table to have between the underscore and the data set number.

I am counting the number of words in the list of the animals and selecting each one as it appears with the %scan function to get one word at a time from the list.

Then in the where clause I am assuming you want one data set when the value matches exactly the animal. The macro value is placed in double quotes so the animal will resolve and be seen by proc sql. If the case does not match the results won't either. If the list has "dog" and the source data set has "Dog" you do not get a match. You may need to use one of the case functions to get comparable values for the desired output.

[RedBishop]

Thanks a lot @ballardw and others!

[RichardDeVen]

Recommendation:

Use WHERE to select data partitions and BY to process them group wise.

Cutting a poor innocent data set into pieces could bring the wrath of PETADS (People for the Ethical Treatment of Data Sets) down on you!

[PaigeMiller]

---

@RichardDeVen wrote:

Recommendation:

Use WHERE to select data partitions and BY to process them group wise.

Cutting a poor innocent data set into pieces could bring the wrath of PETADS (People for the Ethical Treatment of Data Sets) down on you!

---

Yes. There's a reason why SAS doesn't provide a built-in way to split data sets like this; you have to write a macro which is not easy for many people. On the other hand, SAS did build in the BY statement and the WHERE statement to allow handling of different groups in one large data set.

[novinosrin]

Sir @RichardDeVen  and @PaigeMiller , I wish elders like you stipulated rules to adhere over the years unlike what I notice in industry. Sometimes, I feel academia leap frogs so much and on the other hand, industry seems to unwilling to adopt best practices. Kudos for surviving for decades 🙂