BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ursula
Pyrite | Level 9

Hi all,

I tried to fill the blanks with the previous values, as show below:

 

this is the original data look like.

 

ID VISIT value1 value2 value3
A 1 5 7 9
A 2      
A 3      
A 4 10 5 3
A 5      
B 1 11 2 6
B 2      
B 3 16 7 9
B 4      
B 5 18 20 5

 

this is what I want  ---  fill the blank with the previous data.

so A2 is filling with A1 values as well as A3 and so on.

ID VISIT value1 value2 value3
A 1 5 7 9
A 2 5 7 9
A 3 5 7 9
A 4 10 5 3
A 5 10 5 3
B 1 11 2 6
B 2 11 2 6
B 3 16 7 9
B 4 16 7 9
B 5 18 20 5

 

I wonder how to write the codes to get what I want.

Any help will be very appreciate.

 

Thank you,

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

data want; update have (obs=0) have; by id; output; run;

View solution in original post

9 REPLIES 9
Astounding
PROC Star

data want; update have (obs=0) have; by id; output; run;

bvdnberg
Calcite | Level 5
I realy like your solution. It works fine for me. Bur for learning purposes I try to understand why you need the obs=0 option. Can you explain?
nazmul
Quartz | Level 8

Hi, I faced the same problem and tried to use array function. I think I messed up somewhere in the array function. I want to use array function in this problem. Could anyone please tell me what is the problem in my code. 

 

DATA want;
SET have;
array value[*] value1-value3;
array _variable[3];
retain _variable[i];
if not missing(value[i]) then _variable [i] =value [i];
else value [i]=_variable [i];
drop _variable[i];
run;

Steelers_In_DC
Barite | Level 11

Here you go.  If you real data is much different than what you provided there might be a much better route:

 

data have;
infile cards;
input ID$     VISIT     value1     value2     value3;
cards;
A     1     5     7     9
A     2                 
A     3                 
A     4     10     5     3
A     5                 
B     1     11     2     6
B     2                 
B     3     16     7     9
B     4                 
B     5     18     20     5
;

data want;
set have;
retain _value1 _value2 _value3;
if not missing(value1) then _value1 = value1;
if not missing(value2) then _value2 = value2;
if not missing(value3) then _value3 = value3;
if missing(value1) then value1 = _value1;
if missing(value2) then value2 = _value2;
if missing(value3) then value3 = _value3;
drop _:;
run;

ursula
Pyrite | Level 9

thanks so much for the codes.

It works!

 

How about if I have more variables to fill them in, upto 50 variables for example, I know I can use your codes by writing each of the variables names.

I wonder is there any other more simple codes to write upto 50 variables names to fill in?

 

Thanks for your time!

Haikuo
Onyx | Level 15

I bet you haven't tried the solution by @Astounding

ursula
Pyrite | Level 9

Yes, it works!!

It's very helpful!!

 

I really appreciate to all of you for the solution.

you have made the community such a wonderful place to ask question and get the solution.

 

Thanks every body!

 

kannand
Lapis Lazuli | Level 10

Another solution to your requirement would be the use of LAG () function which can help.  Well, you already have a few solutions and perhaps might have this in your pocket available too some day.. 

 

Good Luck to you...!!!

Kannan Deivasigamani
nazmul
Quartz | Level 8

Hi, I faced the same problem and tried to use array function. I think I messed up somewhere in the array function. I want to use array function in this problem. Could anyone please tell me what is the problem in my code. 

 

DATA want;
SET have;
array value[*] value1-value3;
array _variable[3];
retain _variable[i];
if not missing(value[i]) then _variable [i] =value [i];
else value [i]=_variable [i];
drop _variable[i];
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3968 views
  • 12 likes
  • 7 in conversation