Ana Ferreira Ana Ferreira - 5 months ago 11
SQL Question

SAS: How to get the union of a different number of datasets?

I'm running code, almost in an automatic way. I just need to replace one value, and run. But there's one part where I have to do it 'by hand'.

It's the following code:

PROC SQL;
CREATE TABLE DDATA.SUS_151_ALL AS
SELECT * FROM (SELECT * FROM DDATA.RFN_ID673
UNION
SELECT * FROM DDATA.RPFN_ID472
UNION
SELECT * FROM DDATA.RPFN_ID553);
QUIT;


In this case, the clients I want to get the union are here:

PROC SQL NOPRINT;
SELECT EN FROM DDATA.E5P_151;
SELECT COUNT(*) FROM DDATA.E5P_151;
QUIT;


Here, I obtain the following results:

673
472
553
---page break--
3


So, I want something that automatically would read the 3 datasets I wanted to get the union and create the table
DDATA.SUS_151_ALL
.

I have other clients where I have 8 id's to join, and having to do it by hand 100 times takes me some time. I would want just to replace the 151, the source.

For example, for the other client, let's say
id=1000
like this one:

3
7
9
12
16
77
991
1028


I would want a program that would run this:

PROC SQL;
CREATE TABLE DDATA.SUS_1000_ALL AS
SELECT * FROM (SELECT * FROM DDATA.RFN_ID3
UNION
SELECT * FROM DDATA.RPFN_ID7
UNION
SELECT * FROM DDATA.RPFN_ID9
UNION
SELECT * FROM DDATA.RPFN_ID12
UNION
SELECT * FROM DDATA.RPFN_ID16
UNION
SELECT * FROM DDATA.RPFN_ID77
UNION
SELECT * FROM DDATA.RPFN_ID991
UNION
SELECT * FROM DDATA.RPFN_ID1028);
QUIT;


Is this possible? Could you give me some hints?

Kay Kay
Answer

You can make use of macro to build sql code and then execute it in proc sql. Below is the code. Hope it is helpful.

proc sql;
    select EN into :value1 - :value&SysMaxLong from DDATA.E5P_151;
    select count(*) into :cnt from DDATA.E5P_151;
quit;

%global newtext;
%let newtext=;

%macro sqlstmt;

    %do i = 1 %to &cnt;

        %if(&i=&cnt) %then %do;
            %let newtext=&newtext. select * from DDATA.RFN_ID&&value&i ;
        %end;
        %else %do;
            %let newtext=&newtext. select * from DDATA.RFN_ID&&value&i union;
        %end;

        %put newtext=&newtext;
    %end;

%mend sqlstmt;

%sqlstmt

Now add this new macro variable &newtext in the proc sql statement and execute it.

proc sql;
    create table NEW as (&newtext);
quit;