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;
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.