pat | par | vis | base | chg | fac |
101 | let | 10 | 56 | 2 | |
101 | let | 10 | gre | ||
102 | let | 10 | 45 | 4 | |
102 | let | 10 | gre | ||
103 | min | 10 | 44 | 5 | |
104 | min | 10 | 56 | 8 | |
104 | min | 10 | gre |
par
I have the above sample data and want to lag the missing base,chg and fac to the next observation if it is missing and fac =gre.
data inc; set samT; if vis=10 and fac= "" then do; if first.par then do; chg1=lag1(chg); pchg1=lag2(pchg); end; end; run;
Here's a quick and easy way ... if it will do what you want. This will bring forward ALL variables, not just the few that you list.
Assuming your data is sorted by PAT:
data want;
update have (obs=0) have;
by pat;
output;
run;
Do not change the value you are lagging, or base if statements of lag. If you need to drop down values then a retain is far better, or you can use the update function of datastep.
Retain:
data want (drop=lst_base lst_chg); set have; retain lst_base lst_chg; by pat; if first.pat then do; lst_base=base; lst_chg=chg; end; else do; if base=. then base=lst_base; if chg=. then chg=lst_chg; end; run;
**However**, that being said, it looks to me like fac has been merged onto the data somewhere, and has not been merged correctly. You would not for instance have two rows of the same data for vis=10, one with fac, one without. This does not make sense, there should be:
pat | par | vis | base | chg | fac |
101 | let | 10 | 56 | 2 | gre |
And only that row for 101, should it not?
It somehow does'nt work if i use the below code. ot sure where i am doing wrong.
proc sort data=WORK.FILTER_FOR_ADLB_SAS7BDAT_0003 out=inh(keep=usubjid paramcd base chg aval studyid2 visitnum visit); by usubjid paramcd visit; run; data want (drop=lst_base lst_chg); set inh; retain lst_base lst_chg; by paramcd; if first.paramcd and visitnum=10 and aval ne . and studyid2="0A23" then do; lst_base=base; lst_chg=chg; end; else do;
data:
if base=. then base=lst_base; if chg=. then chg=lst_chg; end; run;
usubjid | paramcd | aval | base | chg | visit | visitnum | studyid2 |
101 | Bio | 45 | 42 | 3 | . | 0A23 | |
101 | Bio | 43 | 42 | 1 | . | 0A24 | |
101 | Bio | 45 | . | . | . | 0A23 | |
101 | Bio | 43 | . | . | . | 0A24 | |
101 | Bio | 43 | 45 | -2 | . | 0A24 | |
101 | Bio | 43 | 42 | 1 | Unscheduled | 5.1 | 0A24 |
101 | Bio | 43 | . | . | Unscheduled | 5.1 | 0A24 |
101 | Bio | 43 | 45 | -2 | Unscheduled | 5.1 | 0A24 |
101 | Bio | 45 | 45 | . | Visit 1 (Baseline Extension A) | 1 | 0A24 |
101 | Bio | 42 | 42 | . | Visit 1 (Screening) | 1 | 0A23 |
101 | Bio | 48 | 42 | 6 | Visit 10 (End of Week 😎 | 10 | 0A23 |
101 | Bio | 48 | . | . | Visit 10 (End of Week 😎 | 10 | 0A23 |
101 | Bio | 45 | 42 | 3 | Visit 12 (Completion/Withdrawal) | 12 | 0A23 |
101 | Bio | 45 | . | . | Visit 12 (Completion/Withdrawal) | 12 | 0A23 |
101 | Bio | 45 | 42 | 3 | Visit 6 (End of Week 4) | 6 | 0A23 |
101 | Bio | 45 | . | . | Visit 6 (End of Week 4) | 6 | 0A23 |
Here's a quick and easy way ... if it will do what you want. This will bring forward ALL variables, not just the few that you list.
Assuming your data is sorted by PAT:
data want;
update have (obs=0) have;
by pat;
output;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.