Help using Base SAS procedures

how to create flag based on various criteria for several IDs grouping problem

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

how to create flag based on various criteria for several IDs grouping problem

I have data that's structured like this, with several IDs:

 

ID        linker             date                        diff              flag

A         aaa 000         30Jan2014             0                   1

A         aaa 000         28Feb2014            27                 1

A         aaa 000         29Mar2014            27                  1

A         aab 000         26Apr2014             0                    2

A         aab 000         31May2014             33                 2

A         aab 000         14Oct2014             134                2

A         aac 000          09Jul2014               0                   3

A         aac 000          09Aug2014             29                 3

A         aac 000          15Nov2014              96                3

A         aad 000          10Sep2014             0                   4

 

Diff = (date-1) - (lag(date)+1).  But, it should only be calculated for every second record of a different linker, since that's essentially another group within the ID.  I've set up the flags as such indicating when the linker changes.  However, the next criteria is that if any diff exceeds 62, it becomes its own groups and the ones following it (if they are under 62 become a futher group).  So the new flag should be:

 

ID        linker             date                        diff              flag

A         aaa 000         30Jan2014             0                   1

A         aaa 000         28Feb2014            27                 1

A         aaa 000         29Mar2014            27                  1

A         aab 000         26Apr2014             0                    2

A         aab 000         31May2014             33                 2

A         aab 000         14Oct2014             134                3

A         aac 000          09Jul2014               0                   4

A         aac 000          09Aug2014             29                 4

A         aac 000          15Nov2014              96                5

A         aad 000          10Sep2014             0                   6

 

data test2b;
set test4plus;
flag+0;
do until (last.id);
set test4plus;
by id flag;

if first.id=0 then do;
	if diff > 62 then flag+1;
	else flag+0;

end;

output;
end;
run;

This is the code I tried, but it doesn't accurately output what the flag should be.


Accepted Solutions
Solution
‎01-14-2016 04:04 PM
Valued Guide
Posts: 860

Re: how to create flag based on various criteria for several IDs grouping problem

Posted in reply to appleorange

Here is a solution:

 

data have;
informat id $1. linker $6. date date9.;
format id $1. linker $6. date date9.;
input ID$        linker$             date;
cards;
A         aaa000         30Jan2014
A         aaa000         28Feb2014
A         aaa000         29Mar2014
A         aab000         26Apr2014
A         aab000         31May2014
A         aab000         14Oct2014
A         aac000         09Jul2014
A         aac000         09Aug2014
A         aac000         15Nov2014
A         aad000         10Sep2014
;run;

data want;
set have;
by id linker;
Diff = (date-1) - (lag(date)+1);
if first.linker then diff=0;
if first.linker or diff>62 then flag+1;
run;

View solution in original post


All Replies
Solution
‎01-14-2016 04:04 PM
Valued Guide
Posts: 860

Re: how to create flag based on various criteria for several IDs grouping problem

Posted in reply to appleorange

Here is a solution:

 

data have;
informat id $1. linker $6. date date9.;
format id $1. linker $6. date date9.;
input ID$        linker$             date;
cards;
A         aaa000         30Jan2014
A         aaa000         28Feb2014
A         aaa000         29Mar2014
A         aab000         26Apr2014
A         aab000         31May2014
A         aab000         14Oct2014
A         aac000         09Jul2014
A         aac000         09Aug2014
A         aac000         15Nov2014
A         aad000         10Sep2014
;run;

data want;
set have;
by id linker;
Diff = (date-1) - (lag(date)+1);
if first.linker then diff=0;
if first.linker or diff>62 then flag+1;
run;

Super User
Posts: 11,343

Re: how to create flag based on various criteria for several IDs grouping problem

Posted in reply to appleorange
Not terribly important in the overall scheme but
Diff= dif(date) - 2;
is the same.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 313 views
  • 1 like
  • 3 in conversation