BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GuiVtzl
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

5 REPLIES 5
GuiVtzl
Fluorite | Level 6

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
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6

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
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 5 replies
  • 3444 views
  • 3 likes
  • 3 in conversation