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 !
CC
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
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.
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
CC
Please do also answer my first question.
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
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 !
CC
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.)
*/
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)
;
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) ?
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.