09-05-2016 05:56 PM
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):
CREATE TABLE farrsas.datasetsum as
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.
09-05-2016 09:13 PM
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;
09-05-2016 09:58 PM
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;
09-06-2016 12:23 AM
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.