/* 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.
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.
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.
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.
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.