How to loop a Join ? (Outer Union)

[GuiVtzl]

Hi, I've was wondering how to make a loop to join my tables. Here's the deal, earlier in my program, 4 prompts (Year Start, Year end, Quarter start, Quarter end) are used to settle the number of tables I'll had.

Then I made a loop to create 24 tables (because it goes from Q3_07 to Q2_13).

But now I'd Like to insert a new macro (I suppose I need a loop) to make an Outer Union.

This is the original code, how could I make it automatised using my four prompts ? Thank you a lot:

PROC SQL;

CREATE TABLE CREDITR.APPEND1_ID AS

SELECT * FROM CREDITR.Q3_07_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q4_07_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q1_08_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q2_08_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q3_08_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q4_08_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q1_09_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q2_09_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q3_09_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q4_09_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q1_10_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q2_10_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q3_10_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q4_10_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q1_11_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q2_11_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q3_11_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q4_11_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q1_12_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q2_12_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q3_12_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q4_12_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q1_13_CLE2

OUTER UNION CORR

SELECT * FROM CREDITR.Q2_13_CLE2

  ;

Quit;

[GuiVtzl]

I've got another question; from this :

%MACRO BDD5;

%DO j=&Ystart. %TO &Yend.;

%DO i=1 %to 4;

PROC SQL;

   CREATE TABLE CREDITR.&?_CLE2 AS

   SELECT DISTINCT /* Client */

            t1.THING,

          t1.TEST

      FROM CREDITR.Q&i._&j._TEST t1;

QUIT;   

%END;

%END;

%MEND BDD5;

%BDD5;

My question is : how to use a counter to name each table

1_CLE2

2_CLE2

etc. ?

Thank you

[dhana]

The below macro will generate your SQL code. You can dynamically generate this code for any number of years and quarters. Use the macro calling  %UNIONMAC(07,13,3,2) as per your need.

%MACRO UNIONMAC(START_YEAR, END_YEAR, START_QTR, END_QTR);

%LET X=%SYSFUNC(YYQ(&START_YEAR., &START_QTR.));
%LET Y=%SYSFUNC(YYQ(&END_YEAR., &END_QTR.));
%LET INTERVAL=%SYSFUNC(INTCK(QTR,&X,&Y));

PROC SQL;
CREATE TABLE CREDITR.APPEND1_ID AS

%DO I=0 %TO &INTERVAL;

%LET DATE_INC=%SYSFUNC(INTNX(QTR,&X,&I));
%LET QTR=%SYSFUNC(QTR(&DATE_INC));
%LET YY=%TRIM(%SYSFUNC(YEAR(&DATE_INC)));

%IF &I < &INTERVAL %THEN %DO;
SELECT * FROM CREDITR.Q&QTR._&YY._CLE2
OUTER UNION CORR
%END;
%ELSE %DO;
SELECT * FROM CREDITR.Q&QTR._&YY._CLE2; QUIT;
%END;

%END;

%MEND UNIONMAC(START_YEAR, END_YEAR, START_QTR, END_QTR);

%UNIONMAC(07,13,3,2);

Thanks

Dhana

[Tom · Accepted solution]

Using the INTCK() and INTNX() functions will make it easier to loop.  Why are you using "OUTER UNION CORR" instead of just using a normal data step with a SET statement?

%macro dsname(start,end,prefix,suffix);

%local i s e date year qtr ;

%let s=%sysfunc(inputn(&start,yyq6.),date9.);

%let e=%sysfunc(inputn(&end,yyq6.),date9.);

%do i=0 %to %sysfunc(intck(qtr,"&s"d,"&e"d));

  %let date=%sysfunc(intnx(qtr,"&s"d,&i),yyq4.);

  %let year=%substr(&date,1,2);

  %let qtr=%substr(&date,3,2);

&prefix.&qtr._&year.&suffix

%end;

%mend ;

data CREDITR.APPEND1_ID ;

  set  %dsname(2007Q3,2013Q2,CREDITR.,_CLE2) ;

run;

[GuiVtzl]

Thanks a lot Tom, it works just fine !

I'm creating a new topic with another (and last) problem ! Needless to say that I hope you'll have a look ahah

Thanks again gentlemen

[dhana]

Tom -

Can you please explain how outer union corr and data step set statement appending are different from each other in terms of processing.

Also why cant we use PROC APPEND here.

Thanks

Dhana