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

Hello

I want to use macro in order to create multiple data sets.

1st data set including observations with origin='Asia'

2nd data set including observations with origin='Europe'

3rd data set including observations with any value of origin .

What is the way to update the code in order that the 3rd data set will be created?

thanks

Koe

 

%Macro RRR(value);
Data  tbl_&Value. ;
SET  SASHELP.CARS(where=(origin='&Value."));
Run;
%Mend RRR:
%RRR(value='Asia');
%RRR(value='Europe');
%RRR(value='All');/*I know it is not good code and my question is how should it be written*/

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
%Macro RRR(value);
Data  tbl_&Value. ;
SET  SASHELP.CARS(where=(origin=%sysfunc(ifc(&value=All,origin,"&Value."))));
Run;
%Mend RRR;
%RRR(value=Asia);
%RRR(value=Europe);
%RRR(value=All);/*I know it is not good code and my question is how should it be written*/

LOG:

60   %Macro RRR(value);
61   Data  tbl_&Value. ;
62   SET  SASHELP.CARS(where=(origin=%sysfunc(ifc(&value=All,origin,"&Value."))));
63   Run;
64   %Mend RRR;
65   %RRR(value=Asia);

NOTE: There were 158 observations read from the data set SASHELP.CARS.
      WHERE origin='Asia';
NOTE: The data set WORK.TBL_ASIA has 158 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


66   %RRR(value=Europe);

NOTE: There were 123 observations read from the data set SASHELP.CARS.
      WHERE origin='Europe';
NOTE: The data set WORK.TBL_EUROPE has 123 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


67   %RRR(value=All);

NOTE: There were 428 observations read from the data set SASHELP.CARS.
      WHERE 1 /* an obviously TRUE WHERE clause */ ;
NOTE: The data set WORK.TBL_ALL has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

View solution in original post

12 REPLIES 12
PaigeMiller
Diamond | Level 26

This has been covered in many thread, how to split SAS data sets. Of course, there's no real benefit to doing this, any future analysis to do with your data can be done via BY statements and/or WHERE statements.In addition, the future analysis of the split data sets will require more coding than using one big data set.

 

So the easiest way to write a macro to create multiple data sets is to not split the data sets, which requires no code whatsoever.

--
Paige Miller
Ronein
Meteorite | Level 14

How should the last statement be written?

data Asia Europe All;
   set SASHELP.CARS;
   If origin='Asia' then output Asia;
   If origin=’Europe’ then output Europe;
   IF origin = any value then output All;/*How should be written???*/
run;
Kurt_Bremser
Super User

@Ronein wrote:

How should the last statement be written?

data Asia Europe All;
   set SASHELP.CARS;
   If origin='Asia' then output Asia;
   If origin=’Europe’ then output Europe;
   IF origin = any value then output All;/*How should be written???*/
run;

Just omit the condition and make the output to all unconditional.

Ronein
Meteorite | Level 14

Will it work well by your opinion?

data Asia Europe All;
   set SASHELP.CARS;
   If origin='Asia' then output Asia;
   If origin=’Europe’ then output Europe;
   IF origin=origin  then output All; 
run;
Kurt_Bremser
Super User

First of all, make your code run without any macro coding. Right now, your code won't work in any case.

 

Rule #1 for macro development: start with working Base SAS code.

 


@Ronein wrote:

Hello

I want to use macro in order to create multiple data sets.

1st data set including observations with origin='Asia'

2nd data set including observations with origin='Europe'

3rd data set including observations with any value of origin .

What is the way to update the code in order that the 3rd data set will be created?

thanks

Koe

 

%Macro RRR(value);
Data  tbl_&Value. ;
SET  SASHELP.CARS(where=(origin='&Value."));
Run;
%Mend RRR:
%RRR(value='Asia');
%RRR(value='Europe');
%RRR(value='All');/*I know it is not good code and my question is how should it be written*/

 

 


 

Tom
Super User Tom
Super User

You seem confused by quotes and macro variables.  Your current macro cannot work with the calls you show (even if we ignore the special case).  Your macro wants VALUE to not have quotes in it so you can use it as part of a dataset name.  But then you call it with quotes in it.  So you are generating this code:

Data  tbl_'Asia' ;
SET  SASHELP.CARS(where=(origin="'Asia'"));
Run;

Which will not work in two way. First you cannot have quotes in the dataset name.  And second I doubt that ORIGIN has an values with quotes in them.

 

It would be easiest to write the macro in a way that it generates all of the output in macro call.  In fact this will also allow you to generate all of the outputs in one data step.

Here is an example assuming the values can actually be used as part of the dataset name (like in your example) which is probably NOT a valid assumption for most cases.

%macro RRR(value_list);
%local nitems index value;
%let nitems = %sysfunc(countw(&value_list),%str( ));
data
%do index=1 %to &nitems;
   %let value=%scan(&value_list,&index,%str( ));
   tbl_&value.
%end;
  tbl_other 
;
  SET  SASHELP.CARS ;
  select (origin);
%do index=1 %to &nitems;
   %let value=%scan(&value_list,&index,%str( ));
   when ("&value") output tbl_&value ;
%end;
   otherwise output tbl_other ;
  end;
run;
%mend RRR:
%rrr(Asia Europe)

 

Ronein
Meteorite | Level 14

Thank you

The statement :

otherwise output tbl_other ;

will create dataset with  origin value different than "Asia" or with all values of origin var??

Tom
Super User Tom
Super User

@Ronein wrote:

Thank you

The statement :

otherwise output tbl_other ;

will create dataset with  origin value different than "Asia" or with all values of origin var??


No.  If you want make a subset that is just a copy of the original then make the output statement unconditional so that all observations are written.

 

If your original question is how to make macro parameter that is sometimes used to specify values to select and sometimes means select all of the values then you have a few ways.

 

You could use the special value method that most of the answers on this thread are using.  When the parameter value has a special value then the macro does something different.  You could do that with macro logic that tests for the special value. 

%if "&value" ne "All" %then %do;
  where origin = "&value";
%end;

Or SAS logic that tests for the special value.

where origin="&value" or "&value"="All";

You could add a separate parameter that tells the macro whether to generate the WHERE statement or not.

%macro rrr(value,subset=1);
data tbl_&value;
  set sashelp.cars;
%if &subset=1 %then %do;
  where origin="&value";
%end;
run;
%mend rrr;
%rrr(Asia);
%rrr(All,subset=0);

Or you could let the caller supply the logic instead.  Instead of asking for a value ask for the condition.  In that case you should definitely separate the name of the dataset to generate from the condition used to generate it.

%macro rrr(name,where);
data tbl_&name;
  set sashelp.cars;
  where &where;
run;
%mend rrr;
%rrr(asia,orgin='Asia');
%rrr(all,1=1);

 

novinosrin
Tourmaline | Level 20
%Macro RRR(value);
Data  tbl_&Value. ;
SET  SASHELP.CARS(where=(origin=%sysfunc(ifc(&value=All,origin,"&Value."))));
Run;
%Mend RRR;
%RRR(value=Asia);
%RRR(value=Europe);
%RRR(value=All);/*I know it is not good code and my question is how should it be written*/

LOG:

60   %Macro RRR(value);
61   Data  tbl_&Value. ;
62   SET  SASHELP.CARS(where=(origin=%sysfunc(ifc(&value=All,origin,"&Value."))));
63   Run;
64   %Mend RRR;
65   %RRR(value=Asia);

NOTE: There were 158 observations read from the data set SASHELP.CARS.
      WHERE origin='Asia';
NOTE: The data set WORK.TBL_ASIA has 158 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


66   %RRR(value=Europe);

NOTE: There were 123 observations read from the data set SASHELP.CARS.
      WHERE origin='Europe';
NOTE: The data set WORK.TBL_EUROPE has 123 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


67   %RRR(value=All);

NOTE: There were 428 observations read from the data set SASHELP.CARS.
      WHERE 1 /* an obviously TRUE WHERE clause */ ;
NOTE: The data set WORK.TBL_ALL has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
Ronein
Meteorite | Level 14

One question please.

Why should you use %sysfunc function in this case?

%Macro RRR(value);
Data  tbl_&Value. ;
SET SASHELP.CARS(where=(origin=%sysfunc(ifc(&value=All,origin,"&Value."))));
Run;
%Mend RRR;
%RRR(value=Asia);
%RRR(value=Europe);
%RRR(value=All);

 

novinosrin
Tourmaline | Level 20

%sysfunc was used there to invoke SAS functions, in your case IFC to execute as a compile time operation for the macro processor. Otherwise SAS functions in general would function only at execution time in a Data step/Proc SQL.

 

The logic here uses a simple conditional generation of SAS code using %sysfunc(IFC................)

 

FreelanceReinh
Jade | Level 19

@Ronein wrote:

Why should you use %sysfunc function in this case?


You can write the WHERE condition without %sysfunc as well (note the additional quotes):

origin=ifc("&Value."="All",origin,"&Value.")

Or shorter:

origin="&value" | "&value"="All"

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 12 replies
  • 3883 views
  • 2 likes
  • 6 in conversation