BookmarkSubscribeRSS Feed
jopo12345678
Calcite | Level 5

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!!!!

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
jopo12345678
Calcite | Level 5

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.

jopo12345678
Calcite | Level 5

 

foto2.PNG

 

Here is the screen as result of your code.

art297
Opal | Level 21

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

jopo12345678
Calcite | Level 5

It is numeric field that looks like a  date.

 

foto.PNG

Astounding
PROC Star

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

jopo12345678
Calcite | Level 5

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

Astounding
PROC Star

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.

art297
Opal | Level 21

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

Astounding
PROC Star

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1297 views
  • 0 likes
  • 4 in conversation