BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RedBishop
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

 

 

View solution in original post

10 REPLIES 10
novinosrin
Tourmaline | Level 20

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
SAS Employee

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
Fluorite | Level 6
@novinosrin , almost! This stores 5 copies of the same dataset.
I was looking for one per animal.
novinosrin
Tourmaline | Level 20

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ballardw
Super User

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
Fluorite | Level 6
Thanks a lot @ballardw and others!
RichardDeVen
Barite | Level 11

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
Diamond | Level 26

@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.

--
Paige Miller
novinosrin
Tourmaline | Level 20

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 🙂

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 1355 views
  • 5 likes
  • 6 in conversation