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;
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;
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
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
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;
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
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
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!
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.