Solved: Re: Create tables using date value (looping)

PrudhviB · Posted 02-03-2020 02:23 PM

Hello Sas Experts,

I am looking to loop table name as well as few parameters in the following code:

i have my date range from 201711-202001(this is dynamic)

Currently i have code written for 6 months from 201711-201804 manually (shown for one month)

proc sql;
create table work.Nov2017 as
select t1.MonthYear, 
          t1.product, 
          t1.class, 
          t3.bench, 
          /* product Count */
            (COUNT(t3.bench)) AS 'Product Count'n
			from work.final t1
			LEFT JOIN inventory t2 ON (t1.KEY = t2.KEY) 
           LEFT JOIN WORK.'REFERENCE TBL'n t3 ON (t2.balcode = t3.balcode)
      WHERE t1.MonthYear = '30Nov2017'd AND (1830297600) >= t2.ref_Start_DATE AND (1830297600) < 
           t2.ref_END_DATE
      GROUP BY t1.MonthYear,
               t1.product,
               t1.bench,
               t3.bench
      ORDER BY t1.bench,
               t3.bench;
			   run;

the datetime value is preceding end date 1830297600 is 31 Dec 2017 00:00:00

so here i want to loop these values in bold:

1. create table work.Nov2017

2. WHERE t1.MonthYear = '30Nov2017'd AND (1830297600) >= t2.ref_Start_DATE AND (1830297600)

so basically creating tables from Nov2017 till the last date(dynamic) in the final table.

In advance, thank you for your help.

Kurt_Bremser · Posted 02-06-2020 01:41 AM

After adapting and testing, the code is now

%macro loop;
proc sql noprint;
select max(monthyear) into :lastmonth from work.inventory;
%let cur_month = '30nov2017'd;
%do %while (&cur_month le &lastmonth);
  %let end_date = %sysfunc(intnx(month,&cur_month,1,e));

   CREATE TABLE _%sysfunc(putn(&cur_month,yymmn6.)) AS 
   SELECT t1.MonthYear, 
          t1.Product_grp, 
          t1.cycle AS Current_Cycle, 
          t2.Cycle_dql AS Previous_Cycle, 
          /* Account Count */
            (COUNT(t2.Cycle_dql)) AS Account_Count
      FROM WORK.INVENTORY t1
           LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
      WHERE t1.MonthYear = &cur_month AND &end_date >= datepart(t2.Start_date) AND &end_date < datepart(t2.End_date)
      GROUP BY t1.MonthYear,
               t1.Product_grp,
               t1.cycle,
               t2.Cycle_dql
      ORDER BY t2.Cycle_dql,
               t1.cycle;

  %let cur_month = %sysfunc(intnx(month,&cur_month,1,e));
%end;
quit;
%mend;
%loop

It creates the 5 tables without problems, only for 2018/03 it reports some missing values, so in that period the left join does not find matches for all accounts in delinquency_hist

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

View solution in original post

Pmyosh · Posted 02-03-2020 09:37 PM

Hi, please you clarify what particular dates do you need?
Can you give more samples? Do you mean you need only two date values?

Value1 Value2
30Nov2017 31Dec2017

31Dec2017 31Jan2018

31Jan2018 28Feb2018

Do you mean you want monthend to next month end?

Kurt_Bremser · Posted 02-04-2020 01:47 AM

See this:

%macro loop;
proc sql noprint;
select max(monthyear) into :lastmonth from work.final;
%let cur_month = '30nov2017'd;
%do %while (&cur_month le &lastmonth);
  &end_date = %sysfunc(intnx(month,&cur_month,1,e));
create table _%sysfunc(putn(&cur_month,yymmn6.)) as
select
  t1.monthyear, 
  t1.product, 
  t1.class, 
  t3.bench, 
  /* product Count */
  count(t3.bench) as product_count label='Product Count'
from work.final t1
left join inventory t2
on t1.key = t2.key 
left join work.reference_tbl t3
on t2.balcode = t3.balcode
where
 t1.MonthYear = &cur_month and
 &end_date >= datepart(t2.ref_Start_DATE) and
 &end_date < datepart(t2.ref_END_DATE)
group by
  t1.monthyear,
  t1.product,
  t1.bench,
  t3.bench
order by
  t1.bench,
  t3.bench
;
  %let cur_month = %sysfunc(intnx(month,&cur_month,1,e));
%end;
quit;
%mend;
%loop

name your tables in a way that makes them sort well, so use a YMD order
avoid name literals like the plague, in table and variable names; use labels for fancy text
proc sql does not need a run; sql statements are executed immediately. The procedure is ended with a quit; statement
the code is, of course, untested, for lack of usable example data to test against

Supply example data in a data step with datalines, as shown by @novinosrin in https://communities.sas.com/t5/SAS-Programming/Time-intervals/m-p/603712/highlight/true#M174929

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

PrudhviB · Posted 02-04-2020 01:53 PM

@Kurt_Bremser thank you for this code, i did tried plugging in the tbl names and variables, and its throwing an error at &end_date 180-322 :Statement is not valid or it is used out of proper order.

i am guessing this is occurring when a semicolon is missing but i dont see any missing semicolon. can you assist.

Kurt_Bremser · Posted 02-04-2020 02:03 PM

I missed the %let in the line that is supposed to create end_date.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

ballardw · Posted 02-04-2020 11:27 AM

@PrudhviB wrote:

Hello Sas Experts,

I am looking to loop table name as well as few parameters in the following code:

i have my date range from 201711-202001(this is dynamic)

Currently i have code written for 6 months from 201711-201804 manually (shown for one month)
proc sql;
create table work.Nov2017 as
select t1.MonthYear, 
          t1.product, 
          t1.class, 
          t3.bench, 
          /* product Count */
            (COUNT(t3.bench)) AS 'Product Count'n
			from work.final t1
			LEFT JOIN inventory t2 ON (t1.KEY = t2.KEY) 
           LEFT JOIN WORK.'REFERENCE TBL'n t3 ON (t2.balcode = t3.balcode)
      WHERE t1.MonthYear = '30Nov2017'd AND (1830297600) >= t2.ref_Start_DATE AND (1830297600) < 
           t2.ref_END_DATE
      GROUP BY t1.MonthYear,
               t1.product,
               t1.bench,
               t3.bench
      ORDER BY t1.bench,
               t3.bench;
			   run;
the datetime value is preceding end date 1830297600 is 31 Dec 2017 00:00:00

so here i want to loop these values in bold:

1. create table work.Nov2017

2. WHERE t1.MonthYear = '30Nov2017'd AND (1830297600) >= t2.ref_Start_DATE AND (1830297600)

so basically creating tables from Nov2017 till the last date(dynamic) in the final table.

In advance, thank you for your help.

Can you explain to me what you think the value 1830297600 is contributing to the line of code:

WHERE t1.MonthYear = '30Nov2017'd AND (1830297600) >= t2.ref_Start_DATE AND (1830297600) < 
           t2.ref_END_DATE

I think that if you start with your data and replace the number with just about anything except 0 and missing that you get the exact same result.

PrudhviB · Posted 02-04-2020 01:56 PM

so i have start date and end date in a history table and this date time value is giving me values that are capture in that particular month of whatever the date time value i provide.

PrudhviB · Posted 02-04-2020 12:35 PM

so i have attached the sample project i created to replicate the data that i have and i used for Dec2017 and Jan2018 here as an example to get to the Final Tbl.

with my actual data i have to do this form Nov2017 till date. ( this is where i want to use the looping to create each individual months and then append them to achieve to Final Tbl.

Let me know if this data make sense.

DATA WORK.INVENTORY_0000;
    LENGTH
        MonthYear          8
        Product_grp      $ 7
        accts              8
        cycle              8 ;
    FORMAT
        MonthYear        mmyys7.
        Product_grp      $CHAR7.
        accts            BEST12.
        cycle            BEST12. ;
    INFORMAT
        MonthYear        DATE9.
        Product_grp      $CHAR7.
        accts            BEST12.
        cycle            BEST12. ;
   
    INPUT
        MonthYear        : ANYDTDTE10.
        Product_grp      : $CHAR7.
        accts            : BEST32.
        cycle            : BEST32. ;
		datalines;
30/11/2017 card 1234567 0
30/11/2017 card 2345678 0
30/11/2017 card 3456789 0
30/11/2017 card 4567891 1
30/11/2017 card 5678912 1
30/11/2017 card 6789123 1
30/11/2017 card 7891234 2
30/11/2017 card 8912345 2
30/11/2017 card 9123456 2
31/12/2017 card 4152631 3
31/12/2017 card 1425361 3
31/12/2017 card 7485961 1
31/12/2017 card 4758691 2
31/12/2017 card 2536141 3
31/12/2017 card 5869581 1
31/12/2017 card 4758361 2
31/12/2017 card 2547961 3
31/12/2017 card 6385741 4
31/12/2017 card 9685147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9689147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9644147 2
31/01/2018 lines 9686847 3
31/01/2018 lines 9678947 3
31/01/2018 lines 9682227 3
31/01/2018 lines 9888147 3
31/01/2018 lines 9684677 3
31/01/2018 lines 9685987 5
28/02/2018 lines 9687857 6
28/02/2018 lines 9687957 0
28/02/2018 lines 9687897 1
28/02/2018 lines 9663647 3
28/02/2018 lines 9688897 1
28/02/2018 lines 4152781 2
28/02/2018 lines 4152572 3
28/02/2018 lines 4152573 1
28/02/2018 lines 4152574 2
28/02/2018 lines 4152575 4
31/03/2018 lines 4152576 5
31/03/2018 lines 4152577 4
31/03/2018 lines 4152578 7
31/03/2018 lines 4152571 5
31/03/2018 lines 4152572 6
31/03/2018 lines 4152573 4
31/03/2018 lines 4152574 6
31/03/2018 lines 4152575 5

;
RUN;



DATA WORK.Delinquency_hist;
    LENGTH
        acct_dql           8
        Cycle_dql          8
        Start_date         8
        End_date           8 ;
    FORMAT
        acct_dql         BEST12.
        Cycle_dql        BEST12.
        Start_date       DATETIME21.2
        End_date         DATETIME21.2 ;
    INFORMAT
        acct_dql         BEST12.
        Cycle_dql        BEST12.
        Start_date       DATETIME21.
        End_date         DATETIME21. ;
    INFILE '/apps/sas/saswork/SAS_work541900000CFC_unixl174/#LN00156'
        LRECL=47
        ENCODING="LATIN1"
        TERMSTR=CRLF
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        acct_dql         : BEST32.
        Cycle_dql        : BEST32.
        Start_date       : ANYDTDTM18.
        End_date         : ANYDTDTM18. ;
RUN;

Edit by KB: pulled attachment into code window, so no download necessary.

PrudhviB · Posted 02-04-2020 12:36 PM

@Pmyosh @ballardw @Kurt_Bremser

here is the sample data

Pmyosh · Posted 02-05-2020 01:47 AM

Sorry I can't see the attachment. Can you please re-attach? Thank you

PrudhviB · Posted 02-05-2020 02:57 PM

+attachment

DATA WORK.INVENTORY;
    LENGTH
        MonthYear          8
        Product_grp      $ 7
        accts              8
        cycle              8 ;
    FORMAT
        MonthYear        mmyys7.
        Product_grp      $CHAR7.
        accts            BEST12.
        cycle            BEST12. ;
    INFORMAT
        MonthYear        DATE9.
        Product_grp      $CHAR7.
        accts            BEST12.
        cycle            BEST12. ;
   
    INPUT
        MonthYear        : ANYDTDTE10.
        Product_grp      : $CHAR7.
        accts            : BEST32.
        cycle            : BEST32. ;
		datalines;
30/11/2017 card 1234567 0
30/11/2017 card 2345678 0
30/11/2017 card 3456789 0
30/11/2017 card 4567891 1
30/11/2017 card 5678912 1
30/11/2017 card 6789123 1
30/11/2017 card 7891234 2
30/11/2017 card 8912345 2
30/11/2017 card 9123456 2
31/12/2017 card 4152631 3
31/12/2017 card 1425361 3
31/12/2017 card 7485961 1
31/12/2017 card 4758691 2
31/12/2017 card 2536141 3
31/12/2017 card 5869581 1
31/12/2017 card 4758361 2
31/12/2017 card 2547961 3
31/12/2017 card 6385741 4
31/12/2017 card 9685147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9689147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9644147 2
31/01/2018 lines 9686847 3
31/01/2018 lines 9678947 3
31/01/2018 lines 9682227 3
31/01/2018 lines 9888147 3
31/01/2018 lines 9684677 3
31/01/2018 lines 9685987 5
28/02/2018 lines 9687857 6
28/02/2018 lines 9687957 0
28/02/2018 lines 9687897 1
28/02/2018 lines 9663647 3
28/02/2018 lines 9688897 1
28/02/2018 lines 4152781 2
28/02/2018 lines 4152572 3
28/02/2018 lines 4152573 1
28/02/2018 lines 4152574 2
28/02/2018 lines 4152575 4
31/03/2018 lines 4152576 5
31/03/2018 lines 4152577 4
31/03/2018 lines 4152578 7
31/03/2018 lines 4152571 5
31/03/2018 lines 4152572 6
31/03/2018 lines 4152573 4
31/03/2018 lines 4152574 6
31/03/2018 lines 4152575 5

;
RUN;



DATA WORK.Delinquency_hist;
    LENGTH
        acct_dql           8
        Cycle_dql          8
        Start_date         8
        End_date           8 ;
    FORMAT
        acct_dql         BEST12.
        Cycle_dql        BEST12.
        Start_date       DATETIME21.2
        End_date         DATETIME21.2 ;
    INFORMAT
        acct_dql         BEST12.
        Cycle_dql        BEST12.
        Start_date       DATETIME21.
        End_date         DATETIME21. ;
    
    INPUT
        acct_dql         : BEST32.
        Cycle_dql        : BEST32.
        Start_date       : ANYDTDTM18.
        End_date         : ANYDTDTM18. ;
		datalines;
1234567 1 05DEC2019:00:00:00 03JAN2020:00:00:00
2345678 2 21DEC2019:00:00:00 21JAN2020:00:00:00
3456789 4 31DEC2019:00:00:00 31DEC9999:00:00:00
4567891 2 28DEC2019:00:00:00 01JAN2020:00:00:00
5678912 1 31DEC2019:00:00:00 07JAN2020:00:00:00
6789123 0 10DEC2019:00:00:00 03JAN2020:00:00:00
7891234 0 24DEC2019:00:00:00 21JAN2020:00:00:00
8912345 0 28DEC2019:00:00:00 24JAN2020:00:00:00
9123456 0 25DEC2019:00:00:00 01JAN2020:00:00:00
4152631 0 17DEC2019:00:00:00 03JAN2020:00:00:00
1425361 0 18DEC2019:00:00:00 14JAN2020:00:00:00
7485961 3 31DEC2019:00:00:00 15JAN2020:00:00:00
4758691 5 20DEC2019:00:00:00 09JAN2020:00:00:00
2536141 2 20DEC2019:00:00:00 18JAN2020:00:00:00
5869581 3 31DEC2019:00:00:00 31DEC9999:00:00:00
4758361 2 12DEC2019:00:00:00 03JAN2020:00:00:00
2547961 4 13DEC2019:00:00:00 11JAN2020:00:00:00
6385741 6 24DEC2019:00:00:00 16JAN2020:00:00:00
9685147 0 19DEC2019:00:00:00 08JAN2020:00:00:00
9685147 0 31DEC2019:00:00:00 28JAN2020:00:00:00
4758691 5 30NOV2019:00:00:00 31DEC2019:00:00:00
2536141 0 25DEC2019:00:00:00 01JAN2020:00:00:00
5869581 2 31DEC2019:00:00:00 03JAN2020:00:00:00
4758361 3 14DEC2019:00:00:00 14JAN2020:00:00:00
2547961 0 31DEC2019:00:00:00 04JAN2020:00:00:00
6385741 1 31DEC2019:00:00:00 01JAN2020:00:00:00
9687957 4 30NOV2019:00:00:00 14DEC2019:00:00:00
9687897 2 30NOV2019:00:00:00 24DEC2019:00:00:00
9663647 2 25DEC2019:00:00:00 31DEC9999:00:00:00
8912345 3 28DEC2019:00:00:00 01JAN2020:00:00:00
9123456 0 30NOV2019:00:00:00 11DEC2019:00:00:00
4152631 5 28DEC2019:00:00:00 16JAN2020:00:00:00
1425361 4 31DEC2019:00:00:00 25JAN2020:00:00:00
7485961 5 28DEC2019:00:00:00 10JAN2020:00:00:00
4152572 2 30NOV2019:00:00:00 18DEC2019:00:00:00
4152573 1 28DEC2019:00:00:00 07JAN2020:00:00:00
4152574 4 31DEC2019:00:00:00 04JAN2020:00:00:00
4152575 3 19DEC2019:00:00:00 16JAN2020:00:00:00
4152576 2 31DEC2019:00:00:00 08JAN2020:00:00:00
4152577 2 19DEC2019:00:00:00 04JAN2020:00:00:00
4152578 2 31DEC2019:00:00:00 04JAN2020:00:00:00
4152575 0 25DEC2019:00:00:00 03JAN2020:00:00:00
4152572 0 31DEC2019:00:00:00 10JAN2020:00:00:00
4152577 0 25DEC2019:00:00:00 24JAN2020:00:00:00
4758361 0 31DEC2019:00:00:00 15JAN2020:00:00:00
2547961 0 25DEC2019:00:00:00 09JAN2020:00:00:00
4152631 4 25DEC2019:00:00:00 16JAN2020:00:00:00
1425361 5 14DEC2019:00:00:00 09JAN2020:00:00:00
7485961 1 31DEC2019:00:00:00 07JAN2020:00:00:00
4152572 1 25DEC2019:00:00:00 03JAN2020:00:00:00
4152573 1 28DEC2019:00:00:00 25JAN2020:00:00:00
4152574 3 06DEC2019:00:00:00 04JAN2020:00:00:00
4152575 2 10DEC2019:00:00:00 01JAN2020:00:00:00
4758361 1 30NOV2019:00:00:00 14DEC2019:00:00:00
2547961 2 25DEC2019:00:00:00 24JAN2020:00:00:00
6385741 3 28DEC2019:00:00:00 28JAN2020:00:00:00
9685147 1 17DEC2019:00:00:00 10JAN2020:00:00:00
9687897 2 12APR2018:00:00:00 12MAY2018:00:00:00
9663647 3 13APR2018:00:00:00 15MAY2018:00:00:00
9688897 1 14MAR2019:00:00:00 06APR2019:00:00:00
4152781 2 06OCT2018:00:00:00 06NOV2018:00:00:00
4152631 3 13JUL2019:00:00:00 08AUG2019:00:00:00
1425361 1 02FEB2019:00:00:00 06MAR2019:00:00:00
7485961 2 14MAR2018:00:00:00 12APR2018:00:00:00
4152572 5 13JUL2018:00:00:00 14AUG2018:00:00:00
4152573 4 10APR2018:00:00:00 04MAY2018:00:00:00
4152574 1 12JUN2018:00:00:00 13JUL2018:00:00:00
4152575 2 19APR2018:00:00:00 19MAY2018:00:00:00
2547961 1 14MAR2019:00:00:00 12APR2019:00:00:00
6385741 0 19DEC2018:00:00:00 18JAN2019:00:00:00
9685147 2 19JUL2019:00:00:00 20AUG2019:00:00:00
9687897 2 14MAR2018:00:00:00 12APR2018:00:00:00
6385741 2 28MAR2018:00:00:00 31DEC9999:00:00:00
9687957 0 10APR2018:00:00:00 09MAY2018:00:00:00
9687897 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9663647 0 05DEC2019:00:00:00 04JAN2020:00:00:00
8912345 1 23JAN2018:00:00:00 21FEB2018:00:00:00
9123456 1 29DEC2017:00:00:00 13JAN2018:00:00:00
1425361 2 30MAR2018:00:00:00 13APR2018:00:00:00
7485961 2 01JUN2019:00:00:00 04JUL2019:00:00:00
4152572 2 29DEC2017:00:00:00 30JAN2018:00:00:00
4152573 3 26SEP2018:00:00:00 27OCT2018:00:00:00
6385741 3 30JAN2018:00:00:00 07FEB2018:00:00:00
9687957 4 05JAN2018:00:00:00 06FEB2018:00:00:00
9687897 6 30JAN2018:00:00:00 01MAR2018:00:00:00
9663647 1 27JUL2019:00:00:00 27AUG2019:00:00:00
8912345 3 31JUL2018:00:00:00 16AUG2018:00:00:00
9123456 4 29DEC2017:00:00:00 17JAN2018:00:00:00
4152575 2 30JAN2018:00:00:00 01MAR2018:00:00:00
2547961 2 28MAY2019:00:00:00 26JUN2019:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
1234567 1 05DEC2017:00:00:00 03JAN2020:00:00:00
2345678 2 21DEC2017:00:00:00 21JAN2020:00:00:00
3456789 4 31DEC2017:00:00:00 31DEC9999:00:00:00
4567891 2 28DEC2017:00:00:00 01JAN2020:00:00:00
5678912 1 31DEC2017:00:00:00 07JAN2020:00:00:00
6789123 0 10DEC2017:00:00:00 03JAN2020:00:00:00
7891234 0 24DEC2017:00:00:00 21JAN2020:00:00:00
8912345 0 28DEC2017:00:00:00 24JAN2020:00:00:00
9123456 0 25DEC2017:00:00:00 01JAN2020:00:00:00
4152631 0 17DEC2017:00:00:00 03JAN2020:00:00:00
1425361 0 18DEC2017:00:00:00 14JAN2020:00:00:00
7485961 3 31DEC2017:00:00:00 15JAN2020:00:00:00
4758691 5 20DEC2017:00:00:00 09JAN2020:00:00:00
2536141 2 20DEC2017:00:00:00 18JAN2020:00:00:00
5869581 3 31DEC2017:00:00:00 31DEC9999:00:00:00
4758361 2 12DEC2017:00:00:00 03JAN2020:00:00:00
2547961 4 13DEC2017:00:00:00 11JAN2020:00:00:00
6385741 6 24DEC2017:00:00:00 16JAN2020:00:00:00
9685147 0 17DEC2017:00:00:00 08JAN2020:00:00:00
9685147 0 31DEC2017:00:00:00 28JAN2020:00:00:00
4758691 5 30NOV2017:00:00:00 31DEC2017:00:00:00
2536141 0 25DEC2017:00:00:00 01JAN2020:00:00:00
5869581 2 31DEC2017:00:00:00 03JAN2020:00:00:00
4758361 3 14DEC2017:00:00:00 14JAN2020:00:00:00
2547961 0 31DEC2017:00:00:00 04JAN2020:00:00:00
6385741 1 31DEC2017:00:00:00 01JAN2020:00:00:00
9687957 4 30NOV2017:00:00:00 14DEC2017:00:00:00
9687897 2 30NOV2017:00:00:00 24DEC2017:00:00:00
9663647 2 25DEC2017:00:00:00 31DEC9999:00:00:00
8912345 3 28DEC2017:00:00:00 01JAN2020:00:00:00
9123456 0 30NOV2017:00:00:00 11DEC2017:00:00:00
4152631 5 28DEC2017:00:00:00 16JAN2020:00:00:00
1425361 4 31DEC2017:00:00:00 25JAN2020:00:00:00
7485961 5 28DEC2017:00:00:00 10JAN2020:00:00:00
4152572 2 30NOV2017:00:00:00 18DEC2017:00:00:00
4152573 1 28DEC2017:00:00:00 07JAN2020:00:00:00
4152574 4 31DEC2017:00:00:00 04JAN2020:00:00:00
4152575 3 17DEC2017:00:00:00 16JAN2020:00:00:00
4152576 2 31DEC2017:00:00:00 08JAN2020:00:00:00
4152577 2 17DEC2017:00:00:00 04JAN2020:00:00:00
4152578 2 31DEC2017:00:00:00 04JAN2020:00:00:00
4152575 0 25DEC2017:00:00:00 03JAN2020:00:00:00
4152572 0 31DEC2017:00:00:00 10JAN2020:00:00:00
4152577 0 25DEC2017:00:00:00 24JAN2020:00:00:00
4758361 0 31DEC2017:00:00:00 15JAN2020:00:00:00
2547961 0 25DEC2017:00:00:00 09JAN2020:00:00:00
4152631 4 25DEC2017:00:00:00 16JAN2020:00:00:00
1425361 5 14DEC2017:00:00:00 09JAN2020:00:00:00
7485961 1 31DEC2017:00:00:00 07JAN2020:00:00:00
4152572 1 25DEC2017:00:00:00 03JAN2020:00:00:00
4152573 1 28DEC2017:00:00:00 25JAN2020:00:00:00
4152574 3 06DEC2017:00:00:00 04JAN2020:00:00:00
4152575 2 10DEC2017:00:00:00 01JAN2020:00:00:00
4758361 1 30NOV2017:00:00:00 14DEC2017:00:00:00
2547961 2 25DEC2017:00:00:00 24JAN2020:00:00:00
6385741 3 28DEC2017:00:00:00 28JAN2020:00:00:00
9685147 1 17DEC2017:00:00:00 10JAN2020:00:00:00
9687897 2 12APR2018:00:00:00 12MAY2018:00:00:00
9663647 3 13APR2018:00:00:00 15MAY2018:00:00:00
9688897 1 14MAR2017:00:00:00 06APR2017:00:00:00
4152781 2 06OCT2018:00:00:00 06NOV2018:00:00:00
4152631 3 13JUL2017:00:00:00 08AUG2017:00:00:00
1425361 1 02FEB2017:00:00:00 06MAR2017:00:00:00
7485961 2 14MAR2018:00:00:00 12APR2018:00:00:00
4152572 5 13JUL2018:00:00:00 14AUG2018:00:00:00
4152573 4 10APR2018:00:00:00 04MAY2018:00:00:00
4152574 1 12JUN2018:00:00:00 13JUL2018:00:00:00
4152575 2 17APR2018:00:00:00 17MAY2018:00:00:00
2547961 1 14MAR2017:00:00:00 12APR2017:00:00:00
6385741 0 17DEC2018:00:00:00 18JAN2017:00:00:00
9685147 2 17JUL2017:00:00:00 20AUG2017:00:00:00
9687897 2 14MAR2018:00:00:00 12APR2018:00:00:00
6385741 2 28MAR2018:00:00:00 31DEC9999:00:00:00
9687957 0 10APR2018:00:00:00 09MAY2018:00:00:00
9687897 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9663647 0 05DEC2017:00:00:00 04JAN2020:00:00:00
8912345 1 23JAN2018:00:00:00 21FEB2018:00:00:00
9123456 1 29DEC2017:00:00:00 13JAN2018:00:00:00
1425361 2 30MAR2018:00:00:00 13APR2018:00:00:00
7485961 2 01JUN2017:00:00:00 04JUL2017:00:00:00
4152572 2 29DEC2017:00:00:00 30JAN2018:00:00:00
4152573 3 26SEP2018:00:00:00 27OCT2018:00:00:00
6385741 3 30JAN2018:00:00:00 07FEB2018:00:00:00
9687957 4 05JAN2018:00:00:00 06FEB2018:00:00:00
9687897 6 30JAN2018:00:00:00 01MAR2018:00:00:00
9663647 1 27JUL2017:00:00:00 27AUG2017:00:00:00
8912345 3 31JUL2018:00:00:00 16AUG2018:00:00:00
9123456 4 29DEC2017:00:00:00 17JAN2018:00:00:00
4152575 2 30JAN2018:00:00:00 01MAR2018:00:00:00
2547961 2 28MAY2017:00:00:00 26JUN2017:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
9689147 4 29DEC2017:00:00:00 17JAN2018:00:00:00
9687147 4 30JAN2018:00:00:00 01MAR2018:00:00:00
9644147 2 28MAY2017:00:00:00 26JUN2017:00:00:00
9686847 1 29DEC2017:00:00:00 13JAN2018:00:00:00
9678947 1 29DEC2017:00:00:00 03JAN2018:00:00:00
9682227 1 05DEC2017:00:00:00 04JAN2020:00:00:00
9888147 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9684677 0 05DEC2017:00:00:00 04JAN2020:00:00:00
9685987 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9687857 1 05DEC2017:00:00:00 04JAN2020:00:00:00
9687957 1 29DEC2017:00:00:00 03JAN2018:00:00:00
;
RUN;

PROC SQL;
   CREATE TABLE WORK.Dec2017 AS 
   SELECT t1.MonthYear, 
          t1.Product_grp, 
          t1.cycle AS 'Current Cycle'n, 
          t2.Cycle_dql AS 'Previous Cycle'n, 
          /* Account Count */
            (COUNT(t2.Cycle_dql)) AS 'Account Count'n
      FROM WORK.INVENTORY t1
           LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
      WHERE t1.MonthYear = '31Dec2017'd AND (1827619200) >= t2.Start_date AND (1827619200) < t2.End_date
      GROUP BY t1.MonthYear,
               t1.Product_grp,
               t1.cycle,
               t2.Cycle_dql
      ORDER BY t2.Cycle_dql,
               t1.cycle;
QUIT;
PROC SQL;
   CREATE TABLE WORK.Jan2018 AS 
   SELECT t1.MonthYear, 
          t1.Product_grp, 
          t1.cycle AS 'Current Cycle'n, 
          t2.Cycle_dql AS 'Previous Cycle'n, 
          /* Account Count */
            (COUNT(t2.Cycle_dql)) AS 'Account Count'n
      FROM WORK.INVENTORY t1
           LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
      WHERE t1.MonthYear = '31Jan2018'd AND (1830297600) >= t2.Start_date AND (1830297600) < t2.End_date
      GROUP BY t1.MonthYear,
               t1.Product_grp,
               t1.cycle,
               t2.Cycle_dql
      ORDER BY t2.Cycle_dql,
               t1.cycle;
QUIT;
PROC SQL;
CREATE TABLE WORK.Final_Table AS 
SELECT * FROM WORK.DEC2017
 OUTER UNION CORR 
SELECT * FROM WORK.JAN2018
;
Quit;

Kurt_Bremser · Posted 02-05-2020 02:39 AM

This code will only create one dataset, for the other we would need the text file.

Please post usable examples of ALL THREE datasets used in your initial post.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

PrudhviB · Posted 02-05-2020 02:59 PM

Here is the file @Kurt_Bremser

DATA WORK.INVENTORY;
    LENGTH
        MonthYear          8
        Product_grp      $ 7
        accts              8
        cycle              8 ;
    FORMAT
        MonthYear        mmyys7.
        Product_grp      $CHAR7.
        accts            BEST12.
        cycle            BEST12. ;
    INFORMAT
        MonthYear        DATE9.
        Product_grp      $CHAR7.
        accts            BEST12.
        cycle            BEST12. ;
   
    INPUT
        MonthYear        : ANYDTDTE10.
        Product_grp      : $CHAR7.
        accts            : BEST32.
        cycle            : BEST32. ;
		datalines;
30/11/2017 card 1234567 0
30/11/2017 card 2345678 0
30/11/2017 card 3456789 0
30/11/2017 card 4567891 1
30/11/2017 card 5678912 1
30/11/2017 card 6789123 1
30/11/2017 card 7891234 2
30/11/2017 card 8912345 2
30/11/2017 card 9123456 2
31/12/2017 card 4152631 3
31/12/2017 card 1425361 3
31/12/2017 card 7485961 1
31/12/2017 card 4758691 2
31/12/2017 card 2536141 3
31/12/2017 card 5869581 1
31/12/2017 card 4758361 2
31/12/2017 card 2547961 3
31/12/2017 card 6385741 4
31/12/2017 card 9685147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9689147 2
31/01/2018 lines 9687147 2
31/01/2018 lines 9644147 2
31/01/2018 lines 9686847 3
31/01/2018 lines 9678947 3
31/01/2018 lines 9682227 3
31/01/2018 lines 9888147 3
31/01/2018 lines 9684677 3
31/01/2018 lines 9685987 5
28/02/2018 lines 9687857 6
28/02/2018 lines 9687957 0
28/02/2018 lines 9687897 1
28/02/2018 lines 9663647 3
28/02/2018 lines 9688897 1
28/02/2018 lines 4152781 2
28/02/2018 lines 4152572 3
28/02/2018 lines 4152573 1
28/02/2018 lines 4152574 2
28/02/2018 lines 4152575 4
31/03/2018 lines 4152576 5
31/03/2018 lines 4152577 4
31/03/2018 lines 4152578 7
31/03/2018 lines 4152571 5
31/03/2018 lines 4152572 6
31/03/2018 lines 4152573 4
31/03/2018 lines 4152574 6
31/03/2018 lines 4152575 5

;
RUN;



DATA WORK.Delinquency_hist;
    LENGTH
        acct_dql           8
        Cycle_dql          8
        Start_date         8
        End_date           8 ;
    FORMAT
        acct_dql         BEST12.
        Cycle_dql        BEST12.
        Start_date       DATETIME21.2
        End_date         DATETIME21.2 ;
    INFORMAT
        acct_dql         BEST12.
        Cycle_dql        BEST12.
        Start_date       DATETIME21.
        End_date         DATETIME21. ;
    
    INPUT
        acct_dql         : BEST32.
        Cycle_dql        : BEST32.
        Start_date       : ANYDTDTM18.
        End_date         : ANYDTDTM18. ;
		datalines;
1234567 1 05DEC2019:00:00:00 03JAN2020:00:00:00
2345678 2 21DEC2019:00:00:00 21JAN2020:00:00:00
3456789 4 31DEC2019:00:00:00 31DEC9999:00:00:00
4567891 2 28DEC2019:00:00:00 01JAN2020:00:00:00
5678912 1 31DEC2019:00:00:00 07JAN2020:00:00:00
6789123 0 10DEC2019:00:00:00 03JAN2020:00:00:00
7891234 0 24DEC2019:00:00:00 21JAN2020:00:00:00
8912345 0 28DEC2019:00:00:00 24JAN2020:00:00:00
9123456 0 25DEC2019:00:00:00 01JAN2020:00:00:00
4152631 0 17DEC2019:00:00:00 03JAN2020:00:00:00
1425361 0 18DEC2019:00:00:00 14JAN2020:00:00:00
7485961 3 31DEC2019:00:00:00 15JAN2020:00:00:00
4758691 5 20DEC2019:00:00:00 09JAN2020:00:00:00
2536141 2 20DEC2019:00:00:00 18JAN2020:00:00:00
5869581 3 31DEC2019:00:00:00 31DEC9999:00:00:00
4758361 2 12DEC2019:00:00:00 03JAN2020:00:00:00
2547961 4 13DEC2019:00:00:00 11JAN2020:00:00:00
6385741 6 24DEC2019:00:00:00 16JAN2020:00:00:00
9685147 0 19DEC2019:00:00:00 08JAN2020:00:00:00
9685147 0 31DEC2019:00:00:00 28JAN2020:00:00:00
4758691 5 30NOV2019:00:00:00 31DEC2019:00:00:00
2536141 0 25DEC2019:00:00:00 01JAN2020:00:00:00
5869581 2 31DEC2019:00:00:00 03JAN2020:00:00:00
4758361 3 14DEC2019:00:00:00 14JAN2020:00:00:00
2547961 0 31DEC2019:00:00:00 04JAN2020:00:00:00
6385741 1 31DEC2019:00:00:00 01JAN2020:00:00:00
9687957 4 30NOV2019:00:00:00 14DEC2019:00:00:00
9687897 2 30NOV2019:00:00:00 24DEC2019:00:00:00
9663647 2 25DEC2019:00:00:00 31DEC9999:00:00:00
8912345 3 28DEC2019:00:00:00 01JAN2020:00:00:00
9123456 0 30NOV2019:00:00:00 11DEC2019:00:00:00
4152631 5 28DEC2019:00:00:00 16JAN2020:00:00:00
1425361 4 31DEC2019:00:00:00 25JAN2020:00:00:00
7485961 5 28DEC2019:00:00:00 10JAN2020:00:00:00
4152572 2 30NOV2019:00:00:00 18DEC2019:00:00:00
4152573 1 28DEC2019:00:00:00 07JAN2020:00:00:00
4152574 4 31DEC2019:00:00:00 04JAN2020:00:00:00
4152575 3 19DEC2019:00:00:00 16JAN2020:00:00:00
4152576 2 31DEC2019:00:00:00 08JAN2020:00:00:00
4152577 2 19DEC2019:00:00:00 04JAN2020:00:00:00
4152578 2 31DEC2019:00:00:00 04JAN2020:00:00:00
4152575 0 25DEC2019:00:00:00 03JAN2020:00:00:00
4152572 0 31DEC2019:00:00:00 10JAN2020:00:00:00
4152577 0 25DEC2019:00:00:00 24JAN2020:00:00:00
4758361 0 31DEC2019:00:00:00 15JAN2020:00:00:00
2547961 0 25DEC2019:00:00:00 09JAN2020:00:00:00
4152631 4 25DEC2019:00:00:00 16JAN2020:00:00:00
1425361 5 14DEC2019:00:00:00 09JAN2020:00:00:00
7485961 1 31DEC2019:00:00:00 07JAN2020:00:00:00
4152572 1 25DEC2019:00:00:00 03JAN2020:00:00:00
4152573 1 28DEC2019:00:00:00 25JAN2020:00:00:00
4152574 3 06DEC2019:00:00:00 04JAN2020:00:00:00
4152575 2 10DEC2019:00:00:00 01JAN2020:00:00:00
4758361 1 30NOV2019:00:00:00 14DEC2019:00:00:00
2547961 2 25DEC2019:00:00:00 24JAN2020:00:00:00
6385741 3 28DEC2019:00:00:00 28JAN2020:00:00:00
9685147 1 17DEC2019:00:00:00 10JAN2020:00:00:00
9687897 2 12APR2018:00:00:00 12MAY2018:00:00:00
9663647 3 13APR2018:00:00:00 15MAY2018:00:00:00
9688897 1 14MAR2019:00:00:00 06APR2019:00:00:00
4152781 2 06OCT2018:00:00:00 06NOV2018:00:00:00
4152631 3 13JUL2019:00:00:00 08AUG2019:00:00:00
1425361 1 02FEB2019:00:00:00 06MAR2019:00:00:00
7485961 2 14MAR2018:00:00:00 12APR2018:00:00:00
4152572 5 13JUL2018:00:00:00 14AUG2018:00:00:00
4152573 4 10APR2018:00:00:00 04MAY2018:00:00:00
4152574 1 12JUN2018:00:00:00 13JUL2018:00:00:00
4152575 2 19APR2018:00:00:00 19MAY2018:00:00:00
2547961 1 14MAR2019:00:00:00 12APR2019:00:00:00
6385741 0 19DEC2018:00:00:00 18JAN2019:00:00:00
9685147 2 19JUL2019:00:00:00 20AUG2019:00:00:00
9687897 2 14MAR2018:00:00:00 12APR2018:00:00:00
6385741 2 28MAR2018:00:00:00 31DEC9999:00:00:00
9687957 0 10APR2018:00:00:00 09MAY2018:00:00:00
9687897 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9663647 0 05DEC2019:00:00:00 04JAN2020:00:00:00
8912345 1 23JAN2018:00:00:00 21FEB2018:00:00:00
9123456 1 29DEC2017:00:00:00 13JAN2018:00:00:00
1425361 2 30MAR2018:00:00:00 13APR2018:00:00:00
7485961 2 01JUN2019:00:00:00 04JUL2019:00:00:00
4152572 2 29DEC2017:00:00:00 30JAN2018:00:00:00
4152573 3 26SEP2018:00:00:00 27OCT2018:00:00:00
6385741 3 30JAN2018:00:00:00 07FEB2018:00:00:00
9687957 4 05JAN2018:00:00:00 06FEB2018:00:00:00
9687897 6 30JAN2018:00:00:00 01MAR2018:00:00:00
9663647 1 27JUL2019:00:00:00 27AUG2019:00:00:00
8912345 3 31JUL2018:00:00:00 16AUG2018:00:00:00
9123456 4 29DEC2017:00:00:00 17JAN2018:00:00:00
4152575 2 30JAN2018:00:00:00 01MAR2018:00:00:00
2547961 2 28MAY2019:00:00:00 26JUN2019:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
1234567 1 05DEC2017:00:00:00 03JAN2020:00:00:00
2345678 2 21DEC2017:00:00:00 21JAN2020:00:00:00
3456789 4 31DEC2017:00:00:00 31DEC9999:00:00:00
4567891 2 28DEC2017:00:00:00 01JAN2020:00:00:00
5678912 1 31DEC2017:00:00:00 07JAN2020:00:00:00
6789123 0 10DEC2017:00:00:00 03JAN2020:00:00:00
7891234 0 24DEC2017:00:00:00 21JAN2020:00:00:00
8912345 0 28DEC2017:00:00:00 24JAN2020:00:00:00
9123456 0 25DEC2017:00:00:00 01JAN2020:00:00:00
4152631 0 17DEC2017:00:00:00 03JAN2020:00:00:00
1425361 0 18DEC2017:00:00:00 14JAN2020:00:00:00
7485961 3 31DEC2017:00:00:00 15JAN2020:00:00:00
4758691 5 20DEC2017:00:00:00 09JAN2020:00:00:00
2536141 2 20DEC2017:00:00:00 18JAN2020:00:00:00
5869581 3 31DEC2017:00:00:00 31DEC9999:00:00:00
4758361 2 12DEC2017:00:00:00 03JAN2020:00:00:00
2547961 4 13DEC2017:00:00:00 11JAN2020:00:00:00
6385741 6 24DEC2017:00:00:00 16JAN2020:00:00:00
9685147 0 17DEC2017:00:00:00 08JAN2020:00:00:00
9685147 0 31DEC2017:00:00:00 28JAN2020:00:00:00
4758691 5 30NOV2017:00:00:00 31DEC2017:00:00:00
2536141 0 25DEC2017:00:00:00 01JAN2020:00:00:00
5869581 2 31DEC2017:00:00:00 03JAN2020:00:00:00
4758361 3 14DEC2017:00:00:00 14JAN2020:00:00:00
2547961 0 31DEC2017:00:00:00 04JAN2020:00:00:00
6385741 1 31DEC2017:00:00:00 01JAN2020:00:00:00
9687957 4 30NOV2017:00:00:00 14DEC2017:00:00:00
9687897 2 30NOV2017:00:00:00 24DEC2017:00:00:00
9663647 2 25DEC2017:00:00:00 31DEC9999:00:00:00
8912345 3 28DEC2017:00:00:00 01JAN2020:00:00:00
9123456 0 30NOV2017:00:00:00 11DEC2017:00:00:00
4152631 5 28DEC2017:00:00:00 16JAN2020:00:00:00
1425361 4 31DEC2017:00:00:00 25JAN2020:00:00:00
7485961 5 28DEC2017:00:00:00 10JAN2020:00:00:00
4152572 2 30NOV2017:00:00:00 18DEC2017:00:00:00
4152573 1 28DEC2017:00:00:00 07JAN2020:00:00:00
4152574 4 31DEC2017:00:00:00 04JAN2020:00:00:00
4152575 3 17DEC2017:00:00:00 16JAN2020:00:00:00
4152576 2 31DEC2017:00:00:00 08JAN2020:00:00:00
4152577 2 17DEC2017:00:00:00 04JAN2020:00:00:00
4152578 2 31DEC2017:00:00:00 04JAN2020:00:00:00
4152575 0 25DEC2017:00:00:00 03JAN2020:00:00:00
4152572 0 31DEC2017:00:00:00 10JAN2020:00:00:00
4152577 0 25DEC2017:00:00:00 24JAN2020:00:00:00
4758361 0 31DEC2017:00:00:00 15JAN2020:00:00:00
2547961 0 25DEC2017:00:00:00 09JAN2020:00:00:00
4152631 4 25DEC2017:00:00:00 16JAN2020:00:00:00
1425361 5 14DEC2017:00:00:00 09JAN2020:00:00:00
7485961 1 31DEC2017:00:00:00 07JAN2020:00:00:00
4152572 1 25DEC2017:00:00:00 03JAN2020:00:00:00
4152573 1 28DEC2017:00:00:00 25JAN2020:00:00:00
4152574 3 06DEC2017:00:00:00 04JAN2020:00:00:00
4152575 2 10DEC2017:00:00:00 01JAN2020:00:00:00
4758361 1 30NOV2017:00:00:00 14DEC2017:00:00:00
2547961 2 25DEC2017:00:00:00 24JAN2020:00:00:00
6385741 3 28DEC2017:00:00:00 28JAN2020:00:00:00
9685147 1 17DEC2017:00:00:00 10JAN2020:00:00:00
9687897 2 12APR2018:00:00:00 12MAY2018:00:00:00
9663647 3 13APR2018:00:00:00 15MAY2018:00:00:00
9688897 1 14MAR2017:00:00:00 06APR2017:00:00:00
4152781 2 06OCT2018:00:00:00 06NOV2018:00:00:00
4152631 3 13JUL2017:00:00:00 08AUG2017:00:00:00
1425361 1 02FEB2017:00:00:00 06MAR2017:00:00:00
7485961 2 14MAR2018:00:00:00 12APR2018:00:00:00
4152572 5 13JUL2018:00:00:00 14AUG2018:00:00:00
4152573 4 10APR2018:00:00:00 04MAY2018:00:00:00
4152574 1 12JUN2018:00:00:00 13JUL2018:00:00:00
4152575 2 17APR2018:00:00:00 17MAY2018:00:00:00
2547961 1 14MAR2017:00:00:00 12APR2017:00:00:00
6385741 0 17DEC2018:00:00:00 18JAN2017:00:00:00
9685147 2 17JUL2017:00:00:00 20AUG2017:00:00:00
9687897 2 14MAR2018:00:00:00 12APR2018:00:00:00
6385741 2 28MAR2018:00:00:00 31DEC9999:00:00:00
9687957 0 10APR2018:00:00:00 09MAY2018:00:00:00
9687897 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9663647 0 05DEC2017:00:00:00 04JAN2020:00:00:00
8912345 1 23JAN2018:00:00:00 21FEB2018:00:00:00
9123456 1 29DEC2017:00:00:00 13JAN2018:00:00:00
1425361 2 30MAR2018:00:00:00 13APR2018:00:00:00
7485961 2 01JUN2017:00:00:00 04JUL2017:00:00:00
4152572 2 29DEC2017:00:00:00 30JAN2018:00:00:00
4152573 3 26SEP2018:00:00:00 27OCT2018:00:00:00
6385741 3 30JAN2018:00:00:00 07FEB2018:00:00:00
9687957 4 05JAN2018:00:00:00 06FEB2018:00:00:00
9687897 6 30JAN2018:00:00:00 01MAR2018:00:00:00
9663647 1 27JUL2017:00:00:00 27AUG2017:00:00:00
8912345 3 31JUL2018:00:00:00 16AUG2018:00:00:00
9123456 4 29DEC2017:00:00:00 17JAN2018:00:00:00
4152575 2 30JAN2018:00:00:00 01MAR2018:00:00:00
2547961 2 28MAY2017:00:00:00 26JUN2017:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
6385741 2 29DEC2017:00:00:00 13JAN2018:00:00:00
9689147 4 29DEC2017:00:00:00 17JAN2018:00:00:00
9687147 4 30JAN2018:00:00:00 01MAR2018:00:00:00
9644147 2 28MAY2017:00:00:00 26JUN2017:00:00:00
9686847 1 29DEC2017:00:00:00 13JAN2018:00:00:00
9678947 1 29DEC2017:00:00:00 03JAN2018:00:00:00
9682227 1 05DEC2017:00:00:00 04JAN2020:00:00:00
9888147 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9684677 0 05DEC2017:00:00:00 04JAN2020:00:00:00
9685987 0 29DEC2017:00:00:00 03JAN2018:00:00:00
9687857 1 05DEC2017:00:00:00 04JAN2020:00:00:00
9687957 1 29DEC2017:00:00:00 03JAN2018:00:00:00
;
RUN;

PROC SQL;
   CREATE TABLE WORK.Dec2017 AS 
   SELECT t1.MonthYear, 
          t1.Product_grp, 
          t1.cycle AS 'Current Cycle'n, 
          t2.Cycle_dql AS 'Previous Cycle'n, 
          /* Account Count */
            (COUNT(t2.Cycle_dql)) AS 'Account Count'n
      FROM WORK.INVENTORY t1
           LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
      WHERE t1.MonthYear = '31Dec2017'd AND (1827619200) >= t2.Start_date AND (1827619200) < t2.End_date
      GROUP BY t1.MonthYear,
               t1.Product_grp,
               t1.cycle,
               t2.Cycle_dql
      ORDER BY t2.Cycle_dql,
               t1.cycle;
QUIT;
PROC SQL;
   CREATE TABLE WORK.Jan2018 AS 
   SELECT t1.MonthYear, 
          t1.Product_grp, 
          t1.cycle AS 'Current Cycle'n, 
          t2.Cycle_dql AS 'Previous Cycle'n, 
          /* Account Count */
            (COUNT(t2.Cycle_dql)) AS 'Account Count'n
      FROM WORK.INVENTORY t1
           LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
      WHERE t1.MonthYear = '31Jan2018'd AND (1830297600) >= t2.Start_date AND (1830297600) < t2.End_date
      GROUP BY t1.MonthYear,
               t1.Product_grp,
               t1.cycle,
               t2.Cycle_dql
      ORDER BY t2.Cycle_dql,
               t1.cycle;
QUIT;
PROC SQL;
CREATE TABLE WORK.Final_Table AS 
SELECT * FROM WORK.DEC2017
 OUTER UNION CORR 
SELECT * FROM WORK.JAN2018
;
Quit;

PrudhviB · Posted 02-05-2020 05:20 PM

it should give you 5 tbls as outputs.

Kurt_Bremser · Posted 02-06-2020 01:41 AM

After adapting and testing, the code is now

%macro loop;
proc sql noprint;
select max(monthyear) into :lastmonth from work.inventory;
%let cur_month = '30nov2017'd;
%do %while (&cur_month le &lastmonth);
  %let end_date = %sysfunc(intnx(month,&cur_month,1,e));

   CREATE TABLE _%sysfunc(putn(&cur_month,yymmn6.)) AS 
   SELECT t1.MonthYear, 
          t1.Product_grp, 
          t1.cycle AS Current_Cycle, 
          t2.Cycle_dql AS Previous_Cycle, 
          /* Account Count */
            (COUNT(t2.Cycle_dql)) AS Account_Count
      FROM WORK.INVENTORY t1
           LEFT JOIN WORK.DELINQUENCY_HIST t2 ON (t1.accts = t2.acct_dql)
      WHERE t1.MonthYear = &cur_month AND &end_date >= datepart(t2.Start_date) AND &end_date < datepart(t2.End_date)
      GROUP BY t1.MonthYear,
               t1.Product_grp,
               t1.cycle,
               t2.Cycle_dql
      ORDER BY t2.Cycle_dql,
               t1.cycle;

  %let cur_month = %sysfunc(intnx(month,&cur_month,1,e));
%end;
quit;
%mend;
%loop

It creates the 5 tables without problems, only for 2018/03 it reports some missing values, so in that period the left join does not find matches for all accounts in delinquency_hist

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Classroom Training Available!