compress(Country) !! '; set work.
sales; where Country = "' !! Country !! '"; run;');run;As you can see in the above output, on the left-hand side, we see country-wise unique datasets have been created.
Magic behind the scenesLet’s understand each aspect of the code line by line that makes this magic possible.
data _null_;We set the output data as _null_ because we don’t want to create a single dataset, but multiple datasets at once, which we we’ll see in some time.
set work.
unique;We set the input dataset as work.
unique, which contains unique country names.
We do this because we will want unique country-wise datasets to be created on each iteration.
call execute('data ' !!.compress(Country) !! '; set work.
sales; where Country = "' !! Country !! '"; run;');run;This is the real deal!.You might have noticed that we have written a nested query inside the call execute() statement.
As mentioned earlier, SAS provides this beautiful solution to create multiple datasets without using multiple loops.
Inside the execute() call, we start with the data statement and then append it with our unique country name.
Country here is nothing but the variable from our work.
unique dataset.
The compress function is applied to the Country variable because we have country names with whitespaces in them (for example, New Zealand, United Kingdom).
This compress function removes the whitespace from the country names when creating unique datasets.
Then we put semicolon(;) to end our data step and then set the input dataset as work.
sales.
It is obvious that we set input as our parent dataset work.
sales using which we will create unique datasets.
Then, we put the where condition so that it picks country-wise data from the parent dataset.
Then we end the nested query with the run statement and close the execute() call.
The last run statement of the code is to end the outer query.
Full codeI’m pasting the full SAS code here for your reference.
This SAS code (dynamicMultipleDatasets.
sas) along with the dataset used (Worldwide_Sales.
xls) can be download from GitHub here.
* Importing the dataset in SAS;proc import out=work.
salesdatafile='/folders/myfolders/Worldwide_Sales.
xls'dbms=xls replace;getnames=yes;run;* Extracting country names from the dataset;proc sort data=work.
sales out=work.
unique (keep=Country)nodupkey;by country;run;* Creating multiple datasets from the parent dataset;data _null_;set work.
unique;call execute('data ' !!.compress(Country) !! '; set work.
sales; where Country = "' !! Country !! '"; run;');run;Hope this was helpful.
Please comment your feedback/suggestions.
Cheers!.. More details