topic Re: How to add observation with previous values in SAS Programming

How to add observation with previous values

sasuser123123 — Tue, 26 Nov 2019 09:56:40 GMT

Hello!
I've data where all lab results along with the 8 visits , So some subjects were not visits all 8 visits.So I need to retain the previous visit observation when visit is missing..like

Data new;
Input Id $ visit val1 val2;
Datalines;
ABC123 1 50 54
ABC123 2 33 33
ABC123 3 21 44
ABC123 4 33 64
ABC121 1 90 34
ABC121 2 32 39
ABC121 3 51 24
ABC122 1 73 83
ABC122 2 10 14
ABC122 3 53 77
ABC124 1 50 54
ABC124 2 32 33
ABC124 3 51 94
ABC124 4 33 44
;
Run;

ID ABC121 and ABC122 have only three visits so I need to add 4th visit to those IDs with previous observation values. Could you please help me out how to do this one.

Thank you!
Regards..

Re: How to add observation with previous values

PeterClemmensen — Tue, 26 Nov 2019 10:03:56 GMT

What does your desired output look like?

Re: How to add observation with previous values

Kurt_Bremser — Tue, 26 Nov 2019 10:11:58 GMT

Use by-group processing, and add a loop when last.id is reached:

data have;
input id $ visit val1 val2;
datalines;
ABC123 1 50 54
ABC123 2 33 33
ABC123 3 21 44
ABC123 4 33 64
ABC121 1 90 34
ABC121 2 32 39
ABC121 3 51 24
ABC122 1 73 83
ABC122 2 10 14
ABC122 3 53 77
ABC124 1 50 54
ABC124 2 32 33
ABC124 3 51 94
ABC124 4 33 44
;

data want;
set have;
by id notsorted;
output;
if last.id
then do visit = visit + 1 to 4;
  output;
end;
run;

proc print data=want noobs;
run;

Result:

  id      visit    val1    val2

ABC123      1       50      54 
ABC123      2       33      33 
ABC123      3       21      44 
ABC123      4       33      64 
ABC121      1       90      34 
ABC121      2       32      39 
ABC121      3       51      24 
ABC121      4       51      24 
ABC122      1       73      83 
ABC122      2       10      14 
ABC122      3       53      77 
ABC122      4       53      77 
ABC124      1       50      54 
ABC124      2       32      33 
ABC124      3       51      94 
ABC124      4       33      44

Re: How to add observation with previous values

sasuser123123 — Tue, 26 Nov 2019 10:45:22 GMT

Exactly like this..and it's perfectly working.
Thank you so much for your help..
And could you please explain why we used last.

Re: How to add observation with previous values

sasuser123123 — Tue, 26 Nov 2019 11:25:26 GMT

And also I forget to mention that the variable visit is in character format so what I did was firstly I converted it into numeric and then apply condition then again converted it into character. was this process is correct are is there any alternative......
Thank you!

Re: How to add observation with previous values

Kurt_Bremser — Tue, 26 Nov 2019 11:49:15 GMT

By-group processing is initiated with the by statement; for every variable in the by statement, automatic boolean variables first. and last. are created, which signal when a group starts and when it ends.

My code runs a do loop when the last observation of a group is reached, and the do loop is specified in a way that it only runs when the last observation of a group has a visit number smaller than 4.

Re: How to add observation with previous values

Kurt_Bremser — Tue, 26 Nov 2019 11:51:31 GMT

If you only have values 1 to 4, it makes sense to save space by storing it as $1 (numeric variables need at least 3 bytes).

Still, if you do not have space issues, storing such values as numeric might be better.

Re: How to add observation with previous values

sasuser123123 — Tue, 26 Nov 2019 11:53:07 GMT

Thank you so much !

Re: How to add observation with previous values

sasuser123123 — Wed, 11 Dec 2019 10:48:26 GMT

Hello sir!
I've a doubt...
So if the second observation or third observation is missing instead of fourth observation per id in our raw data how to do locf for that condition like..

data have;
input id $ visit val1 val2;
datalines;
ABC123 1 50 54
ABC123 2 33 33
ABC123 4 33 64
ABC121 1 90 34
ABC121 3 51 24
ABC122 1 73 83
ABC122 2 10 14
ABC122 3 53 77
ABC124 1 50 54
ABC124 2 32 33
ABC124 3 51 94
ABC124 4 33 44
;

Re: How to add observation with previous values

Kurt_Bremser — Wed, 11 Dec 2019 11:39:35 GMT

For this, you use the "look-ahead" technique:

data have;
input id $ visit val1 val2;
datalines;
ABC123 1 50 54
ABC123 2 33 33
ABC123 4 33 64
ABC121 1 90 34
ABC121 3 51 24
ABC122 1 73 83
ABC122 2 10 14
ABC122 3 53 77
ABC124 1 50 54
ABC124 2 32 33
ABC124 3 51 94
ABC124 4 33 44
;

data want;
merge
  have
  have (
    firstobs=2
    keep=id visit
    rename=(id=_id visit=_visit)
  )
;
output;
if id ne _id then _visit = 5;
do visit = visit + 1 to _visit - 1;
  output;
end;
drop _:;
run;

Re: How to add observation with previous values

sasuser123123 — Thu, 12 Dec 2019 05:39:46 GMT

It's very difficult to understand this program...

Re: How to add observation with previous values

sasuser123123 — Thu, 12 Dec 2019 05:49:39 GMT

Anyway thank you so much..

Re: How to add observation with previous values

Kurt_Bremser — Thu, 12 Dec 2019 06:27:39 GMT

I do a merge without a by; the firstobs=2 causes the second read in the merge to always be one observation ahead of the first read.

I only keep two variables and rename them, so I have two additional variables in the PDV, "future" versions of the originals.

One of these is used to detect if we're still in the same group (id), and the other gives me the next visit number.

The do loop is structured in a way that it writes the current observation (one do loop iteration if next visit = current visit + 1), or multiple observations if there is a "hole". At the end of the group, I set a virtual 5th visit, so the loop works right up to visit 4.

This code was created mostly by applying Maxim 4: start with a simple idea (the look-ahead), run it, look where the result differs from the expectations, adapt and run again. Rinse, lather, repeat.

Re: How to add observation with previous values

sasuser123123 — Thu, 12 Dec 2019 06:40:27 GMT

@Kurt_Bremser Thank you so much for your explanation....

Re: How to add observation with previous values

sasuser123123 — Thu, 12 Dec 2019 07:23:39 GMT

And I'm unable to understand that why we used first obs option=2

Re: How to add observation with previous values

Kurt_Bremser — Thu, 12 Dec 2019 07:31:29 GMT

Read the first sentence of my previous post again. Also see the documentation.