topic Re: Flag the longest consecutive coverage in SAS Programming

Flag the longest consecutive coverage

moussa854 — Tue, 04 Dec 2018 16:45:31 GMT

Dear all,

I am new to SAS but I have some programming experience. I would like some help finding the longest consecutive coverage for A and B. As an alternative method I used to include only rows that have yes in all months which made me miss some entries. Appreciate your help.

year

2010

Yes

2011

Yes

2012

Yes

2013

Yes

2011

Yes

2012

Yes

Re: Flag the longest consecutive coverage

novinosrin — Tue, 04 Dec 2018 16:48:29 GMT

What should the output be?

Re: Flag the longest consecutive coverage

moussa854 — Tue, 04 Dec 2018 17:00:09 GMT

For entry A will only include data of 03/2010-11/2011 (21 months coverage)

For entry B will only include data of 01/2011-03/2012 (15 months coverage).

year

2010

Yes

2011

Yes

2012

2013

2011

Yes

2012

Yes

No or blank will be OK

Thanks in advance.

Re: Flag the longest consecutive coverage

mkeintz — Tue, 04 Dec 2018 17:01:33 GMT

Is your sas data already in the layout you show? You'd be better of with a tall-skinny dataset, with one row per month.

Could you provide a DATA step generate the sas data you already have? Then we can provide more useful answers.

Editted addition: as @novinosrin asks, what should the output look like? One row per ID?

Re: Flag the longest consecutive coverage

moussa854 — Tue, 04 Dec 2018 17:07:26 GMT

The data is an upload of csv file in the provided format. The year in one column and the coverage months (Jan-Dec) each has a separate column.

I just want to identify the longest consecutive coverage year and months. Later I will be using it to clean other tables where the date of service is not in the longest consecutive coverage

Best

Re: Flag the longest consecutive coverage

novinosrin — Tue, 04 Dec 2018 17:10:57 GMT

Good morning Mark @mkeintz, I hope your day is going well. I am afraid I am late to college and off to Professor appointment et al priorities. I didn't expect you to pop in early hours but if you have time and wanna take a stab, here is the data have. I will catch up on the thread later. Have a good day!


data have;
informat ID $1. year 4.;
array month(12) $3;
input ID	year month(*);
cards;
A	2010	No	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
A	2011	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No
A	2012	No	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
A	2013	Yes	Yes	No	No	No	No	No	No	No	No	No	No
B	2011	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
B	2012	Yes	Yes	Yes	No	No	No	No	No	No	No	No	No
;

Re: Flag the longest consecutive coverage

PaigeMiller — Tue, 04 Dec 2018 20:52:35 GMT

As @mkeintz stated, you will find that a tall skinny data set is the best way to handle this type of problem in SAS. So we create a tall skinny data set.

Also, as @mkeintz stated, you will get faster and more accurate help if you provide your data as a SAS data step. Fortunately, @novinosrin has done that, but in the future, you should do that yourself.

My solution:

data tall_skinny;
    informat ID $1. year 4.;
    array col(12) $3;
    input ID year col(*);
    do i=1 to 12;
        month=i;
        result=col(i);
        output;
    end;
    keep id year result month;
cards;
A	2010	No	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
A	2011	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No
A	2012	No	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
A	2013	Yes	Yes	No	No	No	No	No	No	No	No	No	No
B	2011	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
B	2012	Yes	Yes	Yes	No	No	No	No	No	No	No	No	No
;

data tall_skinny1;
    set tall_skinny;
    by id;
    if first.id or upcase(result)='NO' then consecutive=0;
    if upcase(result)="YES" then consecutive+1;
run;
proc summary data=tall_skinny1 nway;
    class id;
    var consecutive;
    output out=stats(drop=_:) max= maxid(consecutive(month))=maxmonth 
        maxid(consecutive(year))=maxyear;
run;

/* Select months starting at maxmonth maxyear and go backwards to first month of sequence with coverage */
data final;
    merge tall_skinny1 stats;
    by id;
    if mdy(month,1,year)>intnx('month',mdy(maxmonth,1,maxyear),-consecutive,'b')
        and mdy(month,1,year)<=mdy(maxmonth,1,maxyear);
run;

Re: Flag the longest consecutive coverage

moussa854 — Tue, 04 Dec 2018 19:35:00 GMT

Thank you, Paige, for your answer. I briefly used SAS in the past (6 years ago) and now I am back to it. I am trying to learn it from books and the web. I am trying my best.

This worked:

merge tall_skinny stats;

Thanks again Paige

Re: Flag the longest consecutive coverage

PaigeMiller — Tue, 04 Dec 2018 19:40:55 GMT

Oh yeah, good catch!