topic Re: Concentrate datasets using macro variable in SAS Enterprise Guide

Concentrate datasets using macro variable

ayin — Mon, 20 Mar 2017 23:31:07 GMT

/* Users specify a macro variable */
%let Name = 0 test All
%let Num_Name = %sysfunc(countw(&Name)); /* which is 3 */
%do _z=1 %to &Num_Name;
%let File_Name = %scan(&Name,&_z);

/* datasets have names like */
lib.data_&File_Name

/* in this case, the dataset names would be
lib.data_0
lib.data_test
lib.data_All */

These three datasets have the same columns:

/* e.g. one dataset would look like */
Type _1 _2 _3 Order
A     0  0  0  1

I would like to concentrate these datasets, and create a final dataset: lib.data_final (without hardcoding it)

and then rows arranged in ascending order based on the column 'Order'.

Tried using a loop:

%do _z=1 %to &Num_Name;
%let File_Name = %scan(&Name,&_z);

data lib.want;
set lib.want lib.data_&File_Name;

However reported an error: lib.want.data does not exist.

Re: Concentrate datasets using macro variable

Reeza — Mon, 20 Mar 2017 23:41:24 GMT

By concentrate do you mean concatenate? I'm going to assume so.

We're only seeing part of your code and I suspect that the loop isn't in the correct location. But the %DO loop should be after the SET statement and it's not clear where it's located.

I would suggest an alternative though and that would be to build the name list separately by filtering the SASHELP.VTABLE and then passing that to the macro.

Re: Concentrate datasets using macro variable

Tom — Mon, 20 Mar 2017 23:54:04 GMT

So the user gives you a list of suffixes. What about the beginning of the name? Or the libref?

%let name_list =  0 test All ;
%let prefix=data_ ;
%let libref=lib ;
%let out=want ;

You should first make sure the list has just one space between names. Then it is easy to convert the list of suffixes into a list of dataset names. No %DO loops required.

%let name_list =  %sysfunc(compbl(&name_list)) ;
%let dslist = &libref..&prefix%sysfunc(tranwrd(&name_list,%str( ),%str( &libref..&prefix))) ;

Then if the datasets are already sorted you can just interleave them with a SET/BY.

data &out ;
  set &dslist ;
  by order ;
run;

If they are not sorted then set them together first and then use PROC SORT.

Re: Concentrate datasets using macro variable

ayin — Mon, 20 Mar 2017 23:55:35 GMT

data lib.want;
   %do _z=1 %to &Num_Name;
   %let File_Name = %scan(&Name, &_z);
   set lib.data_&File_Name;
   %end;
run;

Tried this, but only picked up the first dataset lib.data_0; not sure why.

What the code looks like if using your approach?

Thanks.

Re: Concentrate datasets using macro variable

Reeza — Tue, 21 Mar 2017 00:13:50 GMT

Take a look at what you get generated vs what you want:

Generated:

data lib.want;
   set lib.data_0;
   set lib.data_test;
   set lib.data_All;
run;

What you actually want:

data lib.want;
   set lib.data_0  
         lib.data_test 
         lib.data_All;
run;

Note the differences and make the necessary changes.

You can use the following to help you debug in the future.

options MPRINT SYMBOLGEN;

Re: Concentrate datasets using macro variable

ayin — Tue, 21 Mar 2017 01:24:30 GMT

The code's beautiful. Thank you.

Re: Concentrate datasets using macro variable

ballardw — Tue, 21 Mar 2017 06:23:51 GMT

You might want to try your macro with

Let name_list = : ;

That is a colon in case your font is small and hard to see.