Looping table name using date values

[PrudhviB]

Hi this is my code 

PROC SQL;
   CREATE TABLE WORK.Jul2019 AS 
   SELECT t1.MonthYear, 
          t1.FIN_PROD_GRP_CD, 
          t1.'Ref Code'n AS 'Previous Cycle'n, 
          t3.'Ref Code'n AS 'Current Cycle'n, 
          /* Acct Count */
            (COUNT(t3.'Ref Code'n)) AS 'Acct Count'n
      FROM WORK.ALL_INVENTORY t1
           LEFT JOIN NCDSEEXP.V_COLN_ACC_HIST_DLINQ_BCKT t2 ON (t1.ACC_KEY = t2.ACC_KEY) AND (t1.ACC_KEY_SEQ = 
          t2.ACC_KEY_SEQ)
           LEFT JOIN WORK.'REFERENCE TBL'n t3 ON (t2.ACC_PROD_DLINQ_BCKT_CD = t3.'Dlq Code'n)
      WHERE t1.MonthYear = '31Jul2019'd AND (1882828800) >= t2.ACC_PROD_DLINQ_BCKT_START_DATE AND (1882828800) < 
           t2.ACC_PROD_DLINQ_BCKT_END_DATE
      GROUP BY t1.MonthYear,
               t1.FIN_PROD_GRP_CD,
               t1.'Ref Code'n,
               t3.'Ref Code'n
      ORDER BY t1.'Ref Code'n,
               t3.'Ref Code'n;
run;

basically i have my date range from 201711-202001

and i want to loop all the date values in the code. please assist. thank you for your help in advance. 

[ballardw]

Since SQL doesn't actually have any "loop" constructs you might be better off actually showing some small examples of the data that you have and the desired result.

Likely join the data and then use a data step to loop over a given values with a DO loop. But without knowing what you are actually attempting to create I'm not going to attempt to provide any code.

[PrudhviB]

Hi Ballardw,

this is my data 

MonthYear (MMYYS7.)

201711

201712

201801

.

.

.

.

202001

i want to use the monthYear (ranging from 201711-202001) col from this table to create a table names for each value for example :

all the dates with 201711 will be under NOV_2017 and 201805 will be under MAY_2018 

and the date time value is next month end date. for instance in the code i have it for july2019 the date time value is 31st aug 2019 (1882828800). 

so i want to create tables from 201711 till date (which is 202001, approx 27 tables).

eventually i want the output as shown in the image.

[PaigeMiller]

You would be much better off with a single data set containing all months. This would be far less programming, and also run faster. Why make your life so complicated?? Analyses can be done BY MONTHYEAR and no looping is needed.

PROC SQL;
   CREATE TABLE WORK.ALL AS 
   SELECT t1.MonthYear format=yymm5., 
          t1.FIN_PROD_GRP_CD, 
          t1.'Ref Code'n AS 'Previous Cycle'n, 
          t3.'Ref Code'n AS 'Current Cycle'n, 
          /* Acct Count */
            (COUNT(t3.'Ref Code'n)) AS 'Acct Count'n
      FROM WORK.ALL_INVENTORY t1
           LEFT JOIN NCDSEEXP.V_COLN_ACC_HIST_DLINQ_BCKT t2 ON (t1.ACC_KEY = t2.ACC_KEY) AND (t1.ACC_KEY_SEQ = 
          t2.ACC_KEY_SEQ)
           LEFT JOIN WORK.'REFERENCE TBL'n t3 ON (t2.ACC_PROD_DLINQ_BCKT_CD = t3.'Dlq Code'n)
      WHERE t1.MonthYear > '30NOV2017'd AND (1882828800) >= t2.ACC_PROD_DLINQ_BCKT_START_DATE AND (1882828800) < 
           t2.ACC_PROD_DLINQ_BCKT_END_DATE
      GROUP BY t1.MonthYear,
               t1.FIN_PROD_GRP_CD,
               t1.'Ref Code'n,
               t3.'Ref Code'n
      ORDER BY t1.'Ref Code'n,
               t3.'Ref Code'n;
run;

By the way, it would really be helpful if you explained how your desired month range of 201711 to 202001 relates to your code, as I requested earlier. I have made a guess, but I don't really know if I put the right things in the right places. Please explain this.

[PrudhviB]

Hey Miller,

yes i agree analysis by month makes my life much better. let me give you the gist, 

i am building my main table to represent roll rates for collections, which will have current cycle and previous cycle for the same accounts. 

so my logic is break my data into individual months ( which will have monthyear and current cycle values) and join this individual month data back to the bucket tbl (historical data of all delinquencies accounts) to get the previous cycle values ( this is where i use date time value) and then append all the months to get the over all view of roll rates. ( i dont know if there is a better logic i can use to get the previous bucket) .

it is so hard to provide the actual data let me know if this sample makes sense. 

end data looks like 

Month-Year	Product Grp	Previous Cycle	Current Cycle	# of accounts
Jul-19	Grp 1	1	0	60
Jul-19	Grp 2	1	0	23155
Jul-19	Grp 3	1	0	736
Jul-19	Grp 4	1	0	7704
Jul-19	Grp 5	1	0	1436
Jul-19	Grp 6	1	1	783
Jul-19	Grp 1	1	1	10148
Jul-19	Grp 2	1	1	8494
Jul-19	Grp 3	1	1	9
Jul-19	Grp 4	1	1	699
Jul-19	Grp 5	1	12+	25
Jul-19	Grp 6	1	2	10128
Jul-19	Grp 1	1	2	2850
Jul-19	Grp 2	1	2	415
Jul-19	Grp 3	1	2	860
Jul-19	Grp 4	1	2	6899
Jul-19	Grp 5	1	3	255
Jul-19	Grp 6	1	3	1
Jul-19	Grp 1	1	3	74
Jul-19	Grp 2	1	3	7
Jul-19	Grp 3	1	3	110
Jul-19	Grp 4	1	5	5
Jul-19	Grp 5	1	6	1
Jul-19	Grp 6	10	0	7
Jul-19	Grp 1	10	1	1
Jul-19	Grp 2	10	10	1
Jul-19	Grp 3	10	11	6
Jul-19	Grp 4	10	11	65
Jul-19	Grp 5	10	11	3
Jul-19	Grp 6	10	11	3
Jul-19	Grp 1	10	12	6
Jul-19	Grp 2	10	2	1

[Patrick]

Couldn't you just join the table with itself using a join condition like

l.date=intnx('month',r.date,-1)

[PrudhviB]

i tried this but this will not help me as i am trying to get the snapshot (Bucket cycle) of the same account in the previous month. 

[Patrick]

Please try and provide some sample HAVE data, then explain the logic and show us the desired result.

And please provide the sample data via a working SAS data step so we can spend our time for answering your question and not for preparing the sample data.

[PaigeMiller]

---

@Patrick wrote:

Couldn't you just join the table with itself using a join condition like

l.date=intnx('month',r.date,-1)

---

Yes, absolutely, something like this would work and allow one data set to have everything needed that @PrudhviB described. He says this won't work, but doesn't give details or the actual code used, and I'm sure that it would be relatively easy to make this work. But you are correct, @Patrick, we need an example data set in the form of SAS data step code so we can work with this.

[PaigeMiller]

Loop what date values?

Loop where in the code?