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?
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;
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.
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.
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.
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 ;;;;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: