Desktop productivity for business analysts and programmers

Concentrate datasets using macro variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

Concentrate datasets using macro variable

[ Edited ]
/* 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.


Accepted Solutions
Solution
‎03-20-2017 09:24 PM
Super User
Super User
Posts: 6,356

Re: Concentrate datasets using macro variable

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.

View solution in original post


All Replies
Grand Advisor
Posts: 17,361

Re: Concentrate datasets using macro variable

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. 

Frequent Contributor
Posts: 80

Re: Concentrate datasets using macro variable

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.

Grand Advisor
Posts: 17,361

Re: Concentrate datasets using macro variable

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;

 

Solution
‎03-20-2017 09:24 PM
Super User
Super User
Posts: 6,356

Re: Concentrate datasets using macro variable

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.

Frequent Contributor
Posts: 80

Re: Concentrate datasets using macro variable

The code's beautiful. Thank you.
Grand Advisor
Posts: 10,212

Re: Concentrate datasets using macro variable

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.

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 216 views
  • 1 like
  • 4 in conversation