topic Re: Count Consecutive Months with Proc SQL in SAS Programming

Count Consecutive Months with Proc SQL

A_Halps — Tue, 02 Apr 2024 18:37:48 GMT

I am not getting the output I want with my current SQL code within SAS. I need to use Proc SQL for this code -- not allowed to use data steps. I added “row number” and "WHAT I WANT" to the table for ease. Row 4 is not working properly. This is happening throughout the code when I don’t specify the ID as well. I am specifying this ID for the sake of understanding what is going on in the code. For row 4, it should be “2” for Consecutive, not 1 because the row above it is 1 month before. Why is this happening and how do I fix it?

My end goal (which i have not coded for yet) is to look at which IDs have at least 12 consecutive months.

proc sql; 
create table test as 
SELECT year, ID, YearMo
    ,ROW_NUMBER() OVER (PARTITION BY ID, gap ORDER BY yearmo) AS Consecutive
FROM (
SELECT year, ID, YearMo
        ,CASE WHEN yearmo - LAG(yearmo, 1, yearmo - 1) OVER (PARTITION BY ID ORDER BY yearmo) IN (1, 89) 
            THEN 0 ELSE 1 
        END 
+ 
SUM(CASE WHEN yearmo - LAG(yearmo, 1, yearmo - 1) OVER (PARTITION BY ID ORDER BY yearmo) IN (1, 89) 
         THEN 0 ELSE 1 
    END) OVER (PARTITION BY ID ORDER BY yearmo) as gap
    FROM T1
) subquery
where ID = "7"
ORDER BY 
    ID, 
    yearmo;
quit;

Row #	year	ID	YearMo	Consecutive	WHAT I WANT
1	2019	7	201901	1	1
2	2019	7	201902	2	2
3	2019	7	201905	1	1
4	2019	7	201906	1	2
5	2019	7	201908	1	1
6	2019	7	201910	1	1
7	2019	7	201911	2	2
8	2019	7	201912	3	3
9	2020	7	202001	4	4
10	2020	7	202002	5	5
11	2020	7	202003	6	6
12	2020	7	202006	1	1
13	2020	7	202007	2	2
14	2020	7	202008	3	3
15	2020	7	202009	4	4
16	2020	7	202010	5	5
17	2020	7	202011	6	6
18	2020	7	202012	7	7

Re: Count Consecutive Months with Proc SQL

PaigeMiller — Tue, 02 Apr 2024 18:47:03 GMT

I need to use Proc SQL for this code -- not allowed to use data steps.

Very sad, it would be much easier in a data step.

Re: Count Consecutive Months with Proc SQL

ballardw — Tue, 02 Apr 2024 18:57:18 GMT

SAS Proc sql doesn't use Row_number, Partition, Lag , or Over so I have no idea what that code might do other than throw errors. I would be interested in seeing what the log looks like for that code.

At least provide starting data in the form of a working data step and the rules involved. As it is we don't have a clue what the variable types are and since your question involves "consecutive months" that might be kind of crucial to workable solutions.

Re: Count Consecutive Months with Proc SQL

maguiremq — Tue, 02 Apr 2024 19:07:53 GMT

Are you able to pull directly from the database?

If so, you can use pass-through and the code you have listed. I haven't evaluated your actual code - just offering an idea.

Otherwise, it cannot be done with SAS PROC SQL and requires a DATA step as mentioned by @PaigeMiller.

Re: Count Consecutive Months with Proc SQL

Reeza — Tue, 02 Apr 2024 19:25:53 GMT

Google your SQL dialect (postgres SQL + gaps and Islands problem) to find a solution that works for your database.

Examples: https://medium.com/analytics-vidhya/sql-classic-problem-identifying-gaps-and-islands-across-overlapping-date-ranges-5681b5fcdb8#:~:text=Gaps%20and%20islands%20is%20a,sequence%20is%20missing%20(gaps).

https://stackoverflow.com/questions/69978234/add-variable-to-count-consecutive-months

Re: Count Consecutive Months with Proc SQL

PaigeMiller — Wed, 03 Apr 2024 18:55:43 GMT

For anyone reading along who is not required to use SQL, or anyone who is required to use a DATA step, or anyone who wants more efficient code than SQL, here is how I would do this in DATA step

data want;
    set have;
    ym=input(put(yearmo,6.),yymmn6.); /* Convert YYYYMM to an actual SAS date */
    prev_ym=lag(ym);                  /* YM of previous month */
    /* If YM of previous month is one month earlier, then increment value of CONSEC */
    if intck('month',prev_ym,ym)=1 then consec+1;
    /* Otherwise, not consecutive so set consec back to 1 */
    else consec=1;
    format ym yymmn6.;
    drop yearmo prev_ym;
run;