Macro for Splitting one data set into multiple data sets

[mjabed600]

Hello,

I'm a beginner using SAS university edition. I have a data set containing flight information and a variable Country. I am trying to write a macro where i could call the macro with varying values of Country as parameters, for example, %subset("US","CA") or %subset("US","AU","CH"). I tried using the PARMBUFF option but got an error 

ERROR: User does not have appropriate authorization level for library WC000001.

My code is

%macro subsets/parmbuff;
%let num=1;
%let dsname=%scan(&syspbuff, &num);
%do %while(&dsname ne);
   	data &dsname;
   		set myexcel;
   		If country in (&dsname)then output;
   	run;
      proc print data=&dsname;
      run;
%let num=%eval(&num+1);
%let dsname=%scan(&syspbuff, &num);
%end;
%mend subsets;

%subsets("US","CA");

I would appreciate any help to point out my mistakes. thankyou

[Kurt_Bremser]

Being a beginner and fiddling with macro code does not go together well. You need to get some real experience in solving your tasks with the Base SAS code before you venture into macro programming.

A lot of questions here on the forum are a result of people messing with macro code who haven't yet developed a sufficient understanding of the SAS environment.

That said, the macro preprocessor only knows the datatype text, and therefore needs no quotes.

Run this:

%macro subsets/parmbuff;
%let num=1;
%let dsname=%scan(&syspbuff, &num);
%do %while(&dsname ne);
  %put dsname=&dsname;
  %let num=%eval(&num+1);
  %let dsname=%scan(&syspbuff, &num);
%end;
%mend subsets;

%subsets("US","CA");

and you will see that dsname does not contain a valid SAS name, but a string. This causes SAS to try to use "US" as a filename for your dataset, and therefore it needs to assign a temporary library for that. Since "US" contains no path, the temp library points nowhere, so SAS tries to write to the current working directory of the SAS process, where you do not have write permissions.

Splitting datasets usually serves no purpose, as where conditions or by-group processing can do the job much better.

I only use to split datasets if a get a serious performance gain from it, or if I don't have the resources (eg disk space) to process a large dataset in one step.

[RW9 · Accepted solution]

Don't.  It is never a good idea to split one dataset into many, this increases the storage, the code to process it becomes longer, messier and less reliable, and it takes more processing power to process.  I am going to guess from:

myexcel;

That you want an output Excel file, with only certain countries with each on a separate sheet.  This is done very simply by Base SAS - the programming language which you should focus on learning:

%let subset=US AU CH;

/* Get data */
data want;
  set have;
  if country in (&subset.);
run;

/* Output this data to excel - I use tagsets.excelxp but ther are other methods */
ods tagsets.excelxp file="test.xml" options(sheet_interval="bygroup");
proc report data=want nowd;
  by country;
  columns _all_;
run;
ods tagsets.excelxp close;

Note, it worth getting in the habbit of always finishing macro variables with a dot, e.g not this:

&abc

But this:

&abc.

Whilst you get away with it most of the time, there are occurences where not having the dot will cause unexpected results.

[ballardw]

The first step in anything related to macro coding is to get the desired result without any macro code or variables.

Then you have something to start from that works and you can replace one element at a time with a macro variable / parameter.

Did you have code that worked without any macro?

If so show that code.

But creating multiple sets, especially where your value of country might be multiple sets means finding the right set later might be a significant problem all by itself.

[mjabed600]

Thank you all for your input. I guess I over-complicated a simple solution. I'll try to practice my base skills further before jumping into macros.