topic Re: new record based on condition in SAS Programming

new record based on condition

vraj1 — Tue, 09 Oct 2018 12:08:26 GMT

data WORK.INC1;
  infile datalines dsd truncover;
  input ENDT:DATE9. STD:DATE9. menu:$4. id:32. day:32. adj:$200. num:$3.;
datalines4;
07NOV2016,02NOV2016,IDEA,10001,,,2
14NOV2016,08NOV2016,IDEA,10001,1,OVER,3
28NOV2016,15NOV2016,IDEA,10001,,,4
14SEP2016,29NOV2016,IDEA,10001,,,5
;;;;


In the above test data if i have adj="OVER" then i need to create a new record before it i.e in example it is num=3 which has OVER. I need to create num=2 and 
minus number of day in num=2 whci has 07nov2016(end-day) and new record as
07NOV2016,07NOV2016,IDEA,10001,,OVER,2

data WORK.INC12;
  infile datalines dsd truncover;
  input ENDT:DATE9. STD:DATE9. menu:$4. id:32. day:32. adj:$200. num:$3.;
datalines4;
06NOV2016,02NOV2016,IDEA,10001,,,2
07NOV2016,07NOV2016,IDEA,10001,1,OVER,2
14NOV2016,08NOV2016,IDEA,10001,,,3
28NOV2016,15NOV2016,IDEA,10001,,,4
14SEP2016,29NOV2016,IDEA,10001,,,5
;;;;

Can anyone guide me how to get the inc12 dataset attached with input as inc1.

I have made test data for 1 id but they are many id's with different menu.

Any help?

Re: new record based on condition

PeterClemmensen — Tue, 09 Oct 2018 13:29:20 GMT

The logic is a bit tricky, but it gets the job done

data WORK.INC1;
  infile datalines dsd truncover;
  input ENDT:DATE9. STD:DATE9. menu:$4. id:32. day:32. adj:$200. num:$3.;
datalines4;
07NOV2016,02NOV2016,IDEA,10001,,,2
14NOV2016,08NOV2016,IDEA,10001,1,OVER,3
28NOV2016,15NOV2016,IDEA,10001,,,4
14SEP2016,29NOV2016,IDEA,10001,,,5
;;;;

data INC12;
   merge WORK.INC1 WORK.INC1(firstobs=2 keep=adj day STD rename=(adj=lead_adj day=lead_day));

   if lead_adj='OVER' then do;
      ENDT=ENDT-1;
      adj='';
      output;
      ENDT=ENDT+1;
      STD=ENDT;
      adj=lead_adj;day=lead_day;
      output;return;
   end;
   
   adj='';day=.;
   output;
   format ENDT STD lead_STD date9.;
   drop lead:;
run;

Re: new record based on condition

mkeintz — Tue, 09 Oct 2018 14:51:41 GMT

Although I'm a "proponent of auto-merge with lead", I think this problem is more suited to Identify/Revise/Output/Reread:

data WORK.INC1;
  infile datalines dsd truncover;
  input ENDT:DATE9. STD:DATE9. menu:$4. id:32. day:32. adj:$200. num:3.;
datalines4;
07NOV2016,02NOV2016,IDEA,10001,,,2
14NOV2016,08NOV2016,IDEA,10001,1,OVER,3
28NOV2016,15NOV2016,IDEA,10001,,,4
14SEP2016,29NOV2016,IDEA,10001,,,5
;;;;
data want;
  set inc1;
  if adj='OVER' then do;
    endt=endt-7;  /* Revise and output record */
    std=std-1;
    num=num-1;
    output;
    p=_n_;
    set inc1 point=p;  /* Reread the "over" record */
    adj=' ';           /* and modify it */
    day=.;
  end;
  output;
run;

Also, I changed variable NUM to numeric.

Re: new record based on condition

vraj1 — Tue, 09 Oct 2018 14:58:59 GMT

Thanks a lot. Will this work no matter where the OVER record is. sometimes it can be in num 4 or num7?

As i removed firstobs=2 then it didnt work.

Re: new record based on condition

PeterClemmensen — Tue, 09 Oct 2018 15:05:49 GMT

The firstobs=2 options has nothing to do with where the OVER record. Pure coincidence in this case 🙂

So yes, it will work no matter the position.

Re: new record based on condition

vraj1 — Sun, 14 Oct 2018 05:20:07 GMT

It does not work in the below data as no matter what the day has value it should make a new observation. in the below data it should have a new observation in second row something like the below

04NOV2016,02NOV2016,IDEA,10001,,,2
07NOV2016,05NOV2016,IDEA,10001,3,OVER,2
14NOV2016,08NOV2016,IDEA,10001,,,3

data WORK.INC1;
  infile datalines dsd truncover;
  input ENDT:DATE9. STD:DATE9. menu:$4. id:32. day:32. adj:$200. num:3.;
datalines4;
07NOV2016,02NOV2016,IDEA,10001,,,2
14NOV2016,08NOV2016,IDEA,10001,3,OVER,3
28NOV2016,15NOV2016,IDEA,10001,,,4
14SEP2016,29NOV2016,IDEA,10001,,,5
;;;;