topic How to keep the first value of a variable to other observations? in SAS Programming

How to keep the first value of a variable to other observations?

Ada77 — Fri, 12 Jul 2019 22:42:57 GMT

Suppose I have a dataset:

--Have--     
KIND  KIND_CD  var1    var2...    
A     1         1    
A     .         2
A     1         3
.     1         4
.     1         5

All observations belongs to same KIND, but for some reason some values are missing. The dataset I want is:

--want--     
KIND  KIND_CD  var1   var2...   
A     1         1    
A     1         2
A     1         3
A     1         4
A     1         5

I tried to create KIND_1 and KIND_CD_1 to retain the non-missing value. But since I have lots of 'KIND' variables, this may not be a good method.

Re: How to keep the first value of a variable to other observations?

Reeza — Fri, 12 Jul 2019 22:48:17 GMT

It looks like you have read in a report instead of raw data,often where cells are combined via merged cells?

Anways, RETAIN is the correct approach, but how many variables do you need to do this with? If it's a lot an array or macro approach may be needed.

Re: How to keep the first value of a variable to other observations?

Ada77 — Fri, 12 Jul 2019 22:52:56 GMT

I have 8 variable to do that. Do you think make 8 new variable and RETAIN them is suitable here? If I use RETAIN I also should RENAME these new variables afterward

Re: How to keep the first value of a variable to other observations?

novinosrin — Fri, 12 Jul 2019 23:24:19 GMT

Hello @Ada77 Can you please post a comprehensive and better representative sample ?

Re: How to keep the first value of a variable to other observations?

Ada77 — Fri, 12 Jul 2019 23:33:16 GMT

Okay. Actually this is a dataset after merge. Suppose all datas in this dataset should belong to KIND A, but some of them do not match KIND A. I used a indicator to show if they are matched. But I also want to show 'the kind that these obs are supposed to belong to', cause I may stack datasets of different kinds.

--Have--     
KIND  KIND_CD  var1    var2...    match
A     1         1                   1
A     1         2                   1
A     1         3                   1
.     .         4                   0
.     .         5                   0

Hope this will be more clear

Re: How to keep the first value of a variable to other observations?

novinosrin — Fri, 12 Jul 2019 23:36:55 GMT

Please help my understanding.

Are you looking to fill(LOCF) missings only the KIND: group of variables ? or for var: group as well?

Re: How to keep the first value of a variable to other observations?

Ada77 — Fri, 12 Jul 2019 23:39:05 GMT

only KIND: group.
But in actual they do not have same prefix

Re: How to keep the first value of a variable to other observations?

novinosrin — Fri, 12 Jul 2019 23:40:34 GMT

"But in actual they do not have same prefix"

Oh Well, that's the reason I requested a "representative" sample

Re: How to keep the first value of a variable to other observations?

Ada77 — Fri, 12 Jul 2019 23:44:14 GMT

I'm sorry. I used the same prefix just hope it will be more easy to understand

Re: How to keep the first value of a variable to other observations?

novinosrin — Fri, 12 Jul 2019 23:53:54 GMT

If you could provide us a clean sample(mock is fine) with the correct set of var names assuming kind group belong to the same type, i can give it a shot.

Re: How to keep the first value of a variable to other observations?

Ksharp — Sat, 13 Jul 2019 11:51:32 GMT

data have;
input KIND $ KIND_CD  var1;
id=1;
cards; 
A     1         1    
A     .         2
A     1         3
.     1         4
.     1         5
;
run;
data want;
 update have(obs=0) have;
 by id;
 output;
 drop id;
run;

Re: How to keep the first value of a variable to other observations?

Ada77 — Wed, 17 Jul 2019 18:24:04 GMT

Thank you so much! It really helps!