topic Re: using macro to create multiple data sets in SAS Programming

using macro to create multiple data sets

Ronein — Sat, 28 Mar 2020 15:49:36 GMT

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*/

Re: using macro to create multiple data sets

PaigeMiller — Sat, 28 Mar 2020 15:59:37 GMT

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.

Re: using macro to create multiple data sets

Kurt_Bremser — Sat, 28 Mar 2020 16:10:00 GMT

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*/

Re: using macro to create multiple data sets

Ronein — Sat, 28 Mar 2020 16:12:19 GMT

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;

Re: using macro to create multiple data sets

Kurt_Bremser — Sat, 28 Mar 2020 16:14:50 GMT

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

Re: using macro to create multiple data sets

Tom — Sat, 28 Mar 2020 16:16:21 GMT

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)

Re: using macro to create multiple data sets

novinosrin — Sat, 28 Mar 2020 16:23:57 GMT

%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

Re: using macro to create multiple data sets

Ronein — Sat, 28 Mar 2020 16:24:52 GMT

Thank you

The statement :

otherwise output tbl_other ;

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

Re: using macro to create multiple data sets

Ronein — Sat, 28 Mar 2020 16:34:41 GMT

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;

Re: using macro to create multiple data sets

Ronein — Sat, 28 Mar 2020 16:47:23 GMT

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);

Re: using macro to create multiple data sets

novinosrin — Sat, 28 Mar 2020 17:06:08 GMT

%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................)

Re: using macro to create multiple data sets

FreelanceReinh — Sat, 28 Mar 2020 17:34:38 GMT

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

Re: using macro to create multiple data sets

Tom — Sat, 28 Mar 2020 18:13:19 GMT

@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);