BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8
/* 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
Reeza
Super User

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. 

ayin
Quartz | Level 8
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.

Reeza
Super User

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;

 

Tom
Super User Tom
Super User

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.

ayin
Quartz | Level 8
The code's beautiful. Thank you.
ballardw
Super User

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.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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