topic Re: Using PROC SQL iterating on tables using loop in SAS Programming

Using PROC SQL iterating on tables using loop

CC31 — Thu, 03 Jun 2021 12:11:32 GMT

Hello everyone, I'm stuck with a PROC SQL that I'd like to do automatically every month.

I have many tables called "table_YYYYMM" in a library "My_Lib" and my PROC is th following:

LIBNAME My_Lib "path_of_my_library";
PROC SQL;
CREATE TABLE WANT AS
SELECT t1.ID, t2.ID, t3.ID, t4.ID
FROM My_Lib.table_202104 t1
FULL JOIN My_Lib.table_202103 t2 ON (t1.ID = t2.ID)
FULL JOIN My_Lib.table_202102 t3 ON (t1.ID = t3.ID)
FULL JOIN My_Lib.table_202101 t4 ON (t1.ID = t4.ID)

QUIT;

It means that each month I want it to execute it such that every dates that are in the names of tables will change.

Do you have any idea? I have tried with some DO loop but I have some problems to change dates in tables' names.

Thanks a lot in advance !

Re: Using PROC SQL iterating on tables using loop

Kurt_Bremser — Thu, 03 Jun 2021 14:05:30 GMT

Do you always run that for 4 months, or the whole available year, or all datasets available in the library?

Your code will throw a WARNING because ID is already present in the output dataset, and the output dataset will contain one variable only.

Re: Using PROC SQL iterating on tables using loop

CC31 — Thu, 03 Jun 2021 14:15:08 GMT

I run that every 4 months.

Yes sorry I realized that I've made a mistake by copying the code it is in fact

SELECT t1.ID, t2.ID AS ID_2, t3.ID AS ID_3, t4.ID AS ID_4

Sorry about that

Re: Using PROC SQL iterating on tables using loop

Kurt_Bremser — Thu, 03 Jun 2021 14:19:27 GMT

Please do also answer my first question.

Re: Using PROC SQL iterating on tables using loop

CC31 — Thu, 03 Jun 2021 14:21:02 GMT

I run that every months for the last 4 months 🙂

Thank you

Re: Using PROC SQL iterating on tables using loop

Tom — Thu, 03 Jun 2021 14:33:46 GMT

Set the current date and calculate the table names from that.

%let now='01MAY2021'd;
CREATE TABLE WANT AS
SELECT t1.ID as id1, t2.ID as id2, t3.ID as id3, t4.ID as id4
FROM My_Lib.table_%sysfunc(intnx(month,&now,-1),yymm6.) t1
FULL JOIN My_Lib.table_%sysfunc(intnx(month,&now,-2),yymm6.) t2 ON (t1.ID = t2.ID)
FULL JOIN My_Lib.table_%sysfunc(intnx(month,&now,-3),yymm6.) t3 ON (t1.ID = t3.ID)
FULL JOIN My_Lib.table_%sysfunc(intnx(month,&now,-4),yymm6.) t4 ON (t1.ID = t4.ID)
;

Re: Using PROC SQL iterating on tables using loop

Sajid01 — Thu, 03 Jun 2021 14:36:50 GMT

Hello @CC31
With the code in your question as the basis, I am giving a sample code.
This should serve your purpose. Modify / adapt as nee

/*Create dates*/
data _null_;
this_mnth=put(today(),yymmn6.);
call symput('THIS_MNTH',this_mnth);
pre1=put(intnx('month',today(),-1,'b'),yymmn6.);
call symput ("PRE1",pre1);
pre2=put(intnx('month',today(),-2,'b'),yymmn6.);
call symput ("PRE2",pre2);
pre3=put(intnx('month',today(),-3,'b'),yymmn6.);
call symput ("PRE3",pre3);
pre4=put(intnx('month',today(),-4,'b'),yymmn6.);
call symput ("PRE4",pre4);
run;

/* Use the dates*/

LIBNAME My_Lib "path_of_my_library";
PROC SQL;
CREATE TABLE WANT AS
SELECT t1.ID, t2.ID AS ID_2, t3.ID AS ID_3, t4.ID AS ID_4
FROM My_Lib.table_&PRE1 t1
FULL JOIN My_Lib.table_&PRE2. t2 ON (t1.ID = t2.ID)
FULL JOIN My_Lib.table_&PRE3. t3 ON (t1.ID = t3.ID)
FULL JOIN My_Lib.table_&PRE4. t4 ON (t1.ID = t4.ID)

QUIT;
/*
if you need the want table to have a date, then replace want with
want_&THIS_MNTH.)
*/

Re: Using PROC SQL iterating on tables using loop

CC31 — Thu, 03 Jun 2021 14:37:11 GMT

Thanks a lot Tom !

It helps me a lot, I'm quite new at SAS. Do you think that it would be possible to get it in a loop in order to have a nicer code (because I'm thinking about generalizing it to last 12 months also) ?

Re: Using PROC SQL iterating on tables using loop

Kurt_Bremser — Thu, 03 Jun 2021 14:40:13 GMT

Use a macro with a loop:

%let curr=%sysfunc(inputn(202104,yymmn6.));
%macro run_all_four;
oroc sql;
create table want as
  select
    t1.ID as id,
    t2.ID as id_2,
    t3.ID as id_3,
    t4.ID as id_4
  from my_lib.table_%sysfunc(putn(&curr.,yymmn6.)) t1
%do i = 1 %to 3;
  full join my_lib.table_%sysfunc(intnx(month,&curr.,-&i.),yymmn6.) t%eval(&i.+1) on (t1.ID = t%eval(&i.+1).ID)
%end;
;
quit;
%mend;
%run_all_four

Re: Using PROC SQL iterating on tables using loop

CC31 — Thu, 03 Jun 2021 14:45:54 GMT

Many thanks KurtBremser 😃

This is exactly what I wanted, and I can see now where were my misstakes. (it was on the %eval function).

Thanks again !