Help using Base SAS procedures

Retain issue

Reply
Occasional Contributor
Posts: 14

Retain issue

[ Edited ]

Hello everyone,

I have this table without Def_flag colum. I want create a new column Def_flag with value "1" or "0" when the value of column "RAT" is equal to R then 12 months before in colum Def_flag goes value "1" else "0". And for each ID must by separated. Result you can see below.

 

IDDATERATDef_flag
9201705N0
9201704N0
9201703N0
9201702N0
9201701N0
9201612N0
9201611N0
9201610N0
9201609N0
9201608N0
9201607N0
9201606N0
9201605N0
9201604R1
9201603N1
9201602N1
9201601N1
9201512N1
9201511N1
9201510N1
9201509N1
9201508N1
9201507N1
9201506N1
9201505N1
9201504N0
9201503N0
9201502N0
9201501N0
9201412N0
11201705N0
11201704N0
11201703N0
11201702N0
11201701N0
11201612N0
11201611N0
11201610N0
11201609N0
11201608N0
11201607N0
11201606N0
11201605N0
11201604N0
11201603R1
11201602N1
11201601N1
11201512N1
11201511N1
11201510N1
11201509N1
11201508N1
11201507N1
11201506N1
11201505N1
11201504N1
11201503N0
11201502N0
11201501N0
11201412N0

 

Can someone give me a help to create a data step?

Thanks!!!!

Super User
Super User
Posts: 7,977

Re: Retain issue

Posted in reply to jopo12345678

Sort the data in reverse, then when you hit R keep a counter and set the other varible, untested code - post test data as a datastep in future:

proc sort data=have out=want;
  by id descending date;
run;

data want;
  set want;
  retain cnt def_flag;
  by id;
  if first.id or cnt > 12 then do;
    cnt=0;
    chk=0;
    def_flag=0;
  end;
  else if chk then do;
    cnt=cnt+1;
    def_flag=1;
  end;
  else if rat="R" then do;
    chk=1;
    cnt=1;
    def_flag=1;
  end;
run;
Occasional Contributor
Posts: 14

Re: Retain issue

I posted excel data and there is result colum how it should look like. The code is working but value "1" goes to 17 rows insted 12. I made screen of my data.

Occasional Contributor
Posts: 14

Re: Retain issue

Posted in reply to jopo12345678

 

foto2.PNG

 

Here is the screen as result of your code.

PROC Star
Posts: 7,487

Re: Retain issue

Posted in reply to jopo12345678

Solution will depend upon whether date is a date field, a number that looks like a date, or a character field that looks like a numeric date.

 

Here is a method of a number that looks like a date:

data want;
  do until (last.id);
    set have;
    by id;
    if rat eq 'R' then check=date;
  end;
  do until (last.id);
    set have;
    by id;
    if intnx('month',input(put(check,6.),yymmn6.),-11) le
       input(put(date,6.),yymmn6.) le
       intnx('month',input(put(check,6.),yymmn6.),11) then Def_flag=1;
    else Def_flag=0;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 14

Re: Retain issue

[ Edited ]

It is numeric field that looks like a  date.

 

foto.PNG

Super User
Posts: 5,516

Re: Retain issue

Posted in reply to jopo12345678

Can the same ID have more than one "R" observation?

Occasional Contributor
Posts: 14

Re: Retain issue

Posted in reply to Astounding

Yes it can have more the one "R" observation.

Super User
Posts: 5,516

Re: Retain issue

[ Edited ]
Posted in reply to jopo12345678

Here's an approach that can handle multiple "R" records within the same ID.  First, create a map that shows every conceivable record that should be flagged:

 

data flag_me;

set have (keep=id rat);

if rat='R' ;

do obsno = _N_ - 12 to _N_ + 12;

   start = catx('+', id, obsno);

   output;

end;

keep start;

run;

 

This list can include some duplicates, and can include some combinations that won't actually appear in the data.  Those situations won't cause a problem.  Next, dedup:

 

proc sort data=flag_me nodupkey;

by start;

run;

 

Then add the rest of the information needed to convert ID + _N_ into a format:

 

data flag_me;

set flag_me end=done;

retain  fmtname '$def_flag';

label='1' ;

output;

if done;

hlo='O' ;

label='0' ;

output;

run;

 

proc format cntlin=flag_me;

run;

 

Finally, apply the format:

 

data want;

set have;

def_flag = put( catx('+', id, _n_), $def_flag.);

run;

 

This is untested at the moment, I'm hoping I can get to that later.  And it creates DEF_FLAG as a character field.  It can be converted to numeric applying the INPUT function, or alternatively the program could create an INFORMAT instead of a FORMAT.

 

Sorry, disregard this.  It does what I intended it to do, but I was solving the wrong problem.  This flags up to 25 records per R:  the 12 before the "R", the 12 after the "R", and the "R" itself.  I will suggest something else as a separate response.

PROC Star
Posts: 7,487

Re: Retain issue

Posted in reply to jopo12345678

Based on your revised requirements, here is a revised version of my suggested code:

data want (drop=check checkdate);
  set have;
  retain check checkdate;
  by id;
  if first.id then check=0;
  if rat eq 'R' then do;
    check=1;
    checkdate=date;
  end;
  Def_flag=0;
  if check eq 1 then do;
    if intnx('month',input(put(checkdate,6.),yymmn6.),-11) le
       input(put(date,6.),yymmn6.) le
       input(put(checkdate,6.),yymmn6.) then Def_flag=1;
  end;
run;

Art, CEO, AnalystFinder.com

Super User
Posts: 5,516

Re: Retain issue

Posted in reply to jopo12345678

Here's an alternate solution that doesn't involve the dates.  For it to work, the dates have to be complete (no months skipped in the middle) and the observations have to be in order.  It just flags "R" observations and up to 12 observations following each "R" observation.

 

data want;

set have;

by id;

if first.id then r_count=20;

if rat='R' then r_count=0;

r_count + 1;

if r_count <= 13 then def_flag=1;

else def_flag=0;

drop r_count;

run;

 

It wasn't 100% clear whether you wanted to flag the "R" record plus 12 additional records (which this does) or only 11 additional records (which your example illustrates).  But it's easy enough to change the "13" to "12" in this program if need be.

 

Ask a Question
Discussion stats
  • 10 replies
  • 338 views
  • 0 likes
  • 4 in conversation