BookmarkSubscribeRSS Feed
camfarrell25
Quartz | Level 8

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

3 REPLIES 3
rogerjdeangelis
Barite | Level 11
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;


PGStats
Opal | Level 21

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;
PG
Reeza
Super User

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. 

 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

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
  • 3 replies
  • 3456 views
  • 1 like
  • 4 in conversation