BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RonLee
Fluorite | Level 6

Hi everyone, need help coding whether the individual has been there 3 months back to back(jan-mar or feb-apr) for the year. If there are gaps, dont pull include this individuals.  Example below: Individual AA is there from jan-mar contionously so we include him, individual BB has gap so dont include him. Could someone provide any ideas which may help

 

Individual  Date
AA  1/1/2016
AA  2/1/2016
AA  3/1/2016
AA  4/1/2016
AA  7/1/2016
AA  8/1/2016
AA  12/1/2016
BB  2/1/2016
BB  4/1/2016
BB  5/1/2016
BB  7/1/2016
BB  8/1/2016
CC 6/1/2016
CC 7/1/2016
CC 8/1/2016

1 ACCEPTED SOLUTION

Accepted Solutions
LaurieF
Barite | Level 11

If your requirement is that the individual should have had three contiguous months, ever, then this will work:

data source;
infile cards;
attrib individual length=$ 2;
attrib date informat=mmddyy10. format=monyy7.;
input individual 
      date;
cards;
AA  1/1/2016
AA  2/1/2016
AA  3/1/2016
AA  4/1/2016
AA  7/1/2016
AA  8/1/2016
AA  12/1/2016
BB  2/1/2016
BB  4/1/2016
BB  5/1/2016
BB  7/1/2016
BB  8/1/2016
CC 6/1/2016
CC 7/1/2016
CC 8/1/2016
;
run;

data result;
set source;
by individual;
attrib save_date length=4 format=monyy7.;
retain count save_date;
if first.individual 
   then do;
        count = 1;                               /* On the first time, there will always be one! */
        call missing(save_date);     
        end;
   else do;
        if intck('month', save_date, date) = 1   /* Is there a one-month gap? */
           then count + 1;                       /* Increment */
           else count = 1;                       /* Otherwise reset */
        end;
save_date = date;                                /* Save the current observation's date */
if last.individual or count ge 3;                /* Last in the group, or continuous */
if count < 3
   then status = 0;
   else status = 1;
keep individual status;
run;

proc sql;
create table result_summ as
   select individual,
          ifc(max(status) = 1, 'continuous', 'non-continuous') as status_text length=14
     from result
    group by individual;
quit;

Note that it requires two passes - one to flag any three continuous months as 1 (or 0 if they're not contiguous), then the sql pass to get the maximum value of the status. The ifc function returns either of the two text strings, depending on the truth of the condition.

 

Looking at AA: there are three sets of months, Jan/Mar, Feb/Apr and the last three (non-continuous). This creates three observations in result, the first pass, with respective values in status of 11 and 0.

View solution in original post

4 REPLIES 4
ballardw
Super User

It helps to show what the expected output would look like.

RonLee
Fluorite | Level 6


If the desired output could be similiar to this, that would be awesome.

 

Individual Type

AA continuous

BB not_continuous

CC continuous

LaurieF
Barite | Level 11

If your requirement is that the individual should have had three contiguous months, ever, then this will work:

data source;
infile cards;
attrib individual length=$ 2;
attrib date informat=mmddyy10. format=monyy7.;
input individual 
      date;
cards;
AA  1/1/2016
AA  2/1/2016
AA  3/1/2016
AA  4/1/2016
AA  7/1/2016
AA  8/1/2016
AA  12/1/2016
BB  2/1/2016
BB  4/1/2016
BB  5/1/2016
BB  7/1/2016
BB  8/1/2016
CC 6/1/2016
CC 7/1/2016
CC 8/1/2016
;
run;

data result;
set source;
by individual;
attrib save_date length=4 format=monyy7.;
retain count save_date;
if first.individual 
   then do;
        count = 1;                               /* On the first time, there will always be one! */
        call missing(save_date);     
        end;
   else do;
        if intck('month', save_date, date) = 1   /* Is there a one-month gap? */
           then count + 1;                       /* Increment */
           else count = 1;                       /* Otherwise reset */
        end;
save_date = date;                                /* Save the current observation's date */
if last.individual or count ge 3;                /* Last in the group, or continuous */
if count < 3
   then status = 0;
   else status = 1;
keep individual status;
run;

proc sql;
create table result_summ as
   select individual,
          ifc(max(status) = 1, 'continuous', 'non-continuous') as status_text length=14
     from result
    group by individual;
quit;

Note that it requires two passes - one to flag any three continuous months as 1 (or 0 if they're not contiguous), then the sql pass to get the maximum value of the status. The ifc function returns either of the two text strings, depending on the truth of the condition.

 

Looking at AA: there are three sets of months, Jan/Mar, Feb/Apr and the last three (non-continuous). This creates three observations in result, the first pass, with respective values in status of 11 and 0.

RonLee
Fluorite | Level 6
@LaurieF:
Thank you so much, this works perfectly 🙂

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 791 views
  • 1 like
  • 3 in conversation