topic Re: Create tables using date value (looping) in SAS Programming

Create tables using date value (looping)

PrudhviB — Mon, 03 Feb 2020 19:23:15 GMT

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.

Re: Create tables using date value (looping)

Pmyosh — Tue, 04 Feb 2020 02:37:48 GMT

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?

Re: Create tables using date value (looping)

Kurt_Bremser — Tue, 04 Feb 2020 06:47:18 GMT

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

Re: Create tables using date value (looping)

ballardw — Tue, 04 Feb 2020 16:27:56 GMT

@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.

Re: Create tables using date value (looping)

PrudhviB — Wed, 05 Feb 2020 07:15:11 GMT

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.

Re: Create tables using date value (looping)

PrudhviB — Tue, 04 Feb 2020 17:38:11 GMT

@Pmyosh @ballardw @Kurt_Bremser

here is the sample data

Re: Create tables using date value (looping)

PrudhviB — Tue, 04 Feb 2020 18:53:48 GMT

@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.

Re: Create tables using date value (looping)

PrudhviB — Tue, 04 Feb 2020 18:56:28 GMT

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.

Re: Create tables using date value (looping)

Kurt_Bremser — Tue, 04 Feb 2020 19:03:10 GMT

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

Re: Create tables using date value (looping)

Tom — Tue, 04 Feb 2020 19:22:39 GMT

It looks like you have both DATE and DATETIME values in your tables.

Looks like the variable MONTHYEAR has DATE values. Is it always the first day of the month? Or could the value be any day in the month?

It looks like the variables with names like xxx_DATE are actually DATETIME values instead of DATE values. Might reduce confusion to rename them.

What do you mean by PRODUCT COUNT? It looks like you are just counting the number of observations with non-missing values of BENCH. Since you are grouping by BENCH that value will be the same as if you just did COUNT( * ) or COUNT( 1 ) except for any groups created where BENCH is missing, which have count(bench) as zero no matter how many observations there were.

So is the question how you can pass in BEGIN and END date values and have it generate a dataset name and also the appropriate date ranges for the WHERE clause? The first thing to figure out is what parts of the code needs to change. So generate two versions of your query for two different date ranges. Then see where the code is different. Then you can start determining how you can generate the part you need.

You also need to decide how the user is going to provide the BEGIN and END values. Do you want them to provide actual dates? Or do you want them to provide pseudo dates in YYYYMM style like you did in the question?

There are problems with your proposed SQL. You are grouping by two copies of bench, but are not including both of them in the output. Why? What variable in FINAL did you use to find the MAX()? How can the records be both before and after that datetime value? Are you just trying to check if some datetime variable in one of the other two tables is between the start and end datetime values in the inventory table? Which variable?

Re: Create tables using date value (looping)

Pmyosh — Wed, 05 Feb 2020 06:47:40 GMT

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

Re: Create tables using date value (looping)

Kurt_Bremser — Wed, 05 Feb 2020 07:39:14 GMT

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.

Re: Create tables using date value (looping)

PrudhviB — Wed, 05 Feb 2020 19:57:51 GMT

+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;

Re: Create tables using date value (looping)

PrudhviB — Wed, 05 Feb 2020 19:59:23 GMT

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;

Re: Create tables using date value (looping)

PrudhviB — Wed, 05 Feb 2020 22:08:12 GMT

Hi @Tom

i have added my responses below in red.

It looks like you have both DATE and DATETIME values in your tables. Yes

Looks like the variable MONTHYEAR has DATE values. Is it always the first day of the month? Or could the value be any day in the month? It will be a just the last date of the month for all the months (e.g, 30Nov2018)

It looks like the variables with names like xxx_DATE are actually DATETIME values instead of DATE values. Might reduce confusion to rename them. This is coming from my company data table

Also how to you want to name the generated dataset? Looks like you are using the style generated by the MONYY format. Note that is not a good style to use since when you sort it lexicographically it does not sort in chronological order. Also you appear to want to supply both a BEGIN and an END date, so you need to include both dates in the dataset name to avoid confusion. i can name them with number sequence (naming is not my main concern, i can just name them from 1 - 100) and begin date is 01Nov2017 and my end date will be dynamic as or today() )

There are problems with your proposed SQL. You are grouping by two copies of bench, but are not including both of them in the output. Why? sorry in the initial code i have only showed the final code i have attached the updated code bench=cycles and i have them in the output as previous and current cycles

What variable in FINAL did you use to find the MAX() ? MonthYear from Final will be my Max and that would be today() How can the records be both before and after that datetime value? Are you just trying to check if some datetime variable in one of the other two tables is between the start and end datetime values in the inventory table? yes correct Which variable? class variable whihc is acct in the new code.

I have also attached the updated code for better understanding

Re: Create tables using date value (looping)

PrudhviB — Wed, 05 Feb 2020 22:20:05 GMT

it should give you 5 tbls as outputs.

Re: Create tables using date value (looping)

Kurt_Bremser — Thu, 06 Feb 2020 06:41:56 GMT

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

Re: Create tables using date value (looping)

PrudhviB — Wed, 12 Feb 2020 17:20:57 GMT

Thank you @Kurt_Bremser this actually worked for my request. Much appreciate your help