i am trying to assign val variable for the data as first id should have val=12 and second id as val=15 and the rest of id with val=20.
data test; input pid $ date $; cards; 1 1/1/2011 1 1/1/2011
1 1/3/2011 1 3/4/2011 2 5/1/2010 2 6/3/2010 ; run;
Tried something like below but it did not work.
proc sort data=test;
by pid date;
run;
data want;
set test ;
by pid ;
if first.date then val=12;
seq_id+3;
if last.date then val=20;
run;
Need something like below
data need;
input pid $ date $ val ;
cards;
1 1/1/2011 12
1 1/1/2011 15
1 3/4/2011 20
2 5/1/2010 12
2 6/3/2010 15
2 6/4/2010 20
2 12/4/2010 20
2 14/4/2010 20
;
run;
Any guidance please
Hi @noda6003,
Your PROC SORT step is good. Then you could use the LAG and LAG2 functions:
data want;
set test;
val=12+3*(pid=lag(pid))+5*(pid=lag2(pid));
run;
Something like:
data want (drop=seq); set test; retain seq; seq=ifn(first.id,1,seq+1); if seq=1 then val=12; else if seq=2 then val=15; else val=20; run;
Hi @noda6003,
Your PROC SORT step is good. Then you could use the LAG and LAG2 functions:
data want;
set test;
val=12+3*(pid=lag(pid))+5*(pid=lag2(pid));
run;
Thanks a lot and it worked
Do the by-group processing for the correct variable (pid), and use a simple if-then-else:
data have;
input pid $ date :ddmmyy10.;
format date ddmmyy10.;
cards;
1 1/1/2011
1 1/1/2011
1 1/3/2011
1 3/4/2011
2 5/1/2010
2 6/3/2010
2 6/4/2010
2 12/4/2010
2 14/4/2010
;
run;
data want;
set have;
by pid;
retain val;
if first.pid then val = 12;
else if val = 12 then val = 15;
else val = 20;
run;
proc print data=want noobs;
run;
Result:
pid date val 1 01/01/2011 12 1 01/01/2011 15 1 01/03/2011 20 1 03/04/2011 20 2 05/01/2010 12 2 06/03/2010 15 2 06/04/2010 20 2 12/04/2010 20 2 14/04/2010 20
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.