Hello,
I have a sas code where i would like to create a union of multiple datasets, where the title of the dataset is determined by a macrovariable assigned through loop.
What I would like to do is something along those lines (I very well know that this code is not valid but I hope this will help illustrate my logic):
PROC SQL;
CREATE TABLE farrsas.datasetsum as
SELECT *
FROM FARRSAS.dataset&&name&i
UNION
SELECT *
FROM FARRSAS.dataset&&name&i+1
UNION
SELECT *
FROM FARRSAS.dataset&&name&i+2
;
QUIT;
The purpose of the union here is simply to combine the observations of all datasets into one big dataset. Here, the observations in each dataset is specific to &&name&i but what I need is a dataset that combines all observations (if I have 60 observations/rows for name1 and 40 observations/rows for name2 then the resulting dataset is 100 and the number of columns/variables remains the same (as they are common to all datasets). I was under the impression that union would be the best approach but please correct me if I am wrong.
My main challenge here is to have a code such that when it combines all the datasets from i=1 to 18 and doesn't give me an error code when I reach the last dataset.
Thank you,
CF
HAVE 3 datasets
class1 total obs=2
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
class1 total obs=2
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
class1 total obs=2
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
WANT UNION all three
Up to 40 obs from want total obs=6
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Alice F 13 56.5 84.0
3 Alfred M 14 69.0 112.5
4 Alice F 13 56.5 84.0
5 Alfred M 14 69.0 112.5
6 Alice F 13 56.5 84.0
WORKING code
data want;
set
class: ;
run;quit;
FULL SOLUTION
* CREATE SAMPLE DATA
data class1 class2 class3;
set sashelp.class(obs=2);
run;
SECOND SOLUTION;
proc sql;drop table want;quit;
%let stem=class;
%macro itr;
%do i=1 %to 3;
proc append base=want data=&&stem.&i;
run;quit;
%end;
%mend itr;
%itr;
Have you considered using a dataset list within a data step?
data farrsas.dsSum; /* use a name that won't be part of the dataset list */
length source $65;
set farrsas.dataset: indsname=s;
source = s;
run;
In addition to the colon shortcut in @PGStats solution you can reference the datasets with an index as well. The following would append the datasets named class1, class2, ..., class20.
Data want;
Set class1-class20;
Run;
IMO these are much more efficient than any SQL Union solution. Also note unless you use Union All it will erase any duplicates between datasets.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.