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 ... View more

I want to collapse the data within each state to get 1 Record Per ID-State. on top of that, there is a hierarchy for who is the most eligible in that BEN_CD per state. The hierarchy of who should be selected: 1>2>3>4>5>6>7>0.   So, I am combining all of them based on the BEN_CD_#. So for BEN_CD_01: NJ has 1,0,0 so then final result will be 1. For BEN_CD_03: NJ has 1,0,2 so then final result will be 1 because according to my hierarchy, 1 >2>0 For BEN_CD_04: NJ has 3,1,1 so then final result will be 1 because according to my hierarchy, 1 >3 For BEN_CD_07: NJ has 5,6,0 so then final result will be 5 because according to my hierarchy 5>6>0 The code starts like this:  data demogs; input id state_cd $ ben_cd_01-ben_cd_12; datalines; 111 NJ 1 1 1 3 1 0 5 0 0 0 0 0 111 NJ 0 0 0 1 0 1 6 0 0 0 0 0 111 NJ 0 0 2 1 0 1 0 0 0 0 0 0 111 FL 0 0 0 0 0 1 1 1 1 1 1 1 ; run;   Here is some code I have tried to use thus far but am not getting what I want at all: data DEMOGS_COLLAPSED; set DEMOGS; by state_cd; array BENS(12) BENS_CD_01-BENS_CD_12; do i = 01 to 12; if BENS[i] = 0 then BENS[i] = 99; end; drop i; run; data DEMOGS_COLLAPSED_2; set DEMOGS_COLLAPSED; by state_cd; array BENS_rst(12) BENS_CD_01-BENS_CD_12; do i = 01 to 12; if BENS_rst[i] ne . then BENS_rst[i]=min(BENS_rst[i]); end; drop i; run;   and I want it to end like this: data final; input id state_cd $ ben_cd_01-ben_cd_12; datalines; 111 NJ 1 1 1 1 1 1 5 0 0 0 0 0 111 FL 0 0 0 0 0 1 1 1 1 1 1 1 ; run;   Thanks in advance! ... View more