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 |
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.
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;
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.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.