topic Loop through union in SAS Programming

Loop through union

camfarrell25 — Mon, 05 Sep 2016 21:56:29 GMT

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,

Re: Loop through union

rogerjdeangelis — Tue, 06 Sep 2016 01:13:37 GMT

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;

Re: Loop through union

PGStats — Tue, 06 Sep 2016 01:58:32 GMT

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;

Re: Loop through union

Reeza — Tue, 06 Sep 2016 04:23:29 GMT

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.