topic Re: LAST VALUE CARRY FORWARD in SAS Programming

LAST VALUE CARRY FORWARD

sahoositaram555 — Thu, 02 Apr 2020 20:48:10 GMT

Hi, I have a dataset where i have do last value carry forward by downword rows.

Any leads would be helpful.

data CM;

input USUBJID mmmol_injected;cards;

AUS-001001 520
AUS-001001 0
AUS-001001 .
AUS-001001 .

AUS-001001 .

AUS-001001 100
US-001001 0
US-001001 .

US-001001 100

US-001001 .

US-001001 100

US-001001 .

PAK-001001 70

PAK-001001 80

PAK-001001 0

PAK-001001 .

PAK-001001 80

;

run;

Re: LAST VALUE CARRY FORWARD

PeterClemmensen — Thu, 02 Apr 2020 20:49:31 GMT

You want the last non-missing value carried forward by USUBJID, correct?

Re: LAST VALUE CARRY FORWARD

sahoositaram555 — Thu, 02 Apr 2020 20:53:09 GMT

Sorry dear @PeterClemmensen,
it has a visitn column as well. So i have to do it by subjid & visitn .
data CM;

input USUBJID visitn mmmol_injected;cards;

AUS-001001 1 520
AUS-001001 2 0
AUS-001001 3 .
AUS-001001 4 .
AUS-001001 5 .
AUS-001001 6 100
US-001001 1 0
US-001001 2 .
US-001001 3 100
US-001001 4 .
US-001001 5 100
US-001001 6 .
PAK-001001 1 70
PAK-001001 2 80
PAK-001001 3 0
PAK-001001 4 0
PAK-001001 5 .
PAK-001001 6 80
;

run;

Re: LAST VALUE CARRY FORWARD

PeterClemmensen — Thu, 02 Apr 2020 20:54:13 GMT

I'm not sure I understand the requirement. What does your desired result look like?

Re: LAST VALUE CARRY FORWARD

sahoositaram555 — Thu, 02 Apr 2020 20:57:10 GMT

Dear @PeterClemmensen,
the resulted data set should look like
AUS-001001 1 520
AUS-001001 2 0
AUS-001001 3 0
AUS-001001 4 0
AUS-001001 5 0
AUS-001001 6 100
US-001001 1 0 100
US-001001 2 100
US-001001 3 100
US-001001 4 100
US-001001 5 100
US-001001 6 100
PAK-001001 1 70
PAK-001001 2 80
PAK-001001 3 0
PAK-001001 4 0
PAK-001001 5 0
PAK-001001 6 80

basically if there is a row blank the value from the upper row must be copied to that place

Re: LAST VALUE CARRY FORWARD

PeterClemmensen — Thu, 02 Apr 2020 21:01:23 GMT

Ok. Try this

data CM;
input USUBJID $10. mmmol_injected;
cards;
AUS-001001  520
AUS-001001  0
AUS-001001  .
AUS-001001  .
AUS-001001  .
AUS-001001  100
US-001001    0
US-001001    .
US-001001    100
US-001001    .
US-001001    100
US-001001    .
PAK-001001  70
PAK-001001  80
PAK-001001  0
PAK-001001  0
PAK-001001  .
PAK-001001  80
;
run;

data want (drop=_:);
   set CM;
   by USUBJID notsorted;
   retain _mm;
   if first.USUBJID then _mm=.;
   if mmmol_injected ne . then _mm=mmmol_injected;
   else if mmmol_injected=. then mmmol_injected=_mm;
run;

Re: LAST VALUE CARRY FORWARD

sahoositaram555 — Thu, 02 Apr 2020 21:06:15 GMT

Thank you very much @PeterClemmensen. it worked.