SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Carrying non-missing values down a by group for all numeric and character variables in arrays and do

Reply
Occasional Contributor
Posts: 16

Carrying non-missing values down a by group for all numeric and character variables in arrays and do

[ Edited ]

Hello!

I'm trying to carry non-missing values down a by group for multiple numeric and character columns at the same time, but I'm having trouble. It is carrying forward, but it is applying the same value from the first column to all of the columns. I have a feeling that I'm miss-using the retain command on the temp variable. So I guess the question is: sIs there a way I can update the temp in these do loops so it fills values only from the appropriate column? Here's the syntax I've used:

 

 

 

data have;

input id group rate rate2 name $ number location $;

datalines;

1 1 5 6 Rick . USA

1 1 . . . 2 .

1 1 2 3 Stan . UK

1 2 . . . . . 

1 2 . . . 4 .

1 2 . . Sarah .  Spain

1 2 . . . . .

1 2 . . . . .

1 3 . . . Bf . Poland

1 3 3 . . . .

1 3 . . . . .

 

 

data get;

set have;

by group;

array Nums[*] _numeric_;

array Chars[*] _character_;

do i= 1 to dim(nums);

retain temp;

if first.group then temp=.;

if nums[i] ne . then temp=nums[i];

else if nums[i]=. then nums[i]=temp;

end;

do i= 1 to dim(Chars);

retain temp;

if first.group then temp=.;

if Chars[i] ne . then temp=NUMs[i];

else if Chars[i]=. then Chars[i]=temp;

end; run;

 

 

 

 

 

What I get with the syntax:

 

1 1 5 6 Rick . USA

1 1 5 5 5 2 5

1 1 2 3 Stan 5 UK

1 2 5 5 5 5 5

1 2 5 5 5 4 5

1 2 5 5 Sarah 5 Spain

1 2  5 5 5 5 5

1 2 5 5 5 5 5

1 3 5 5 5 Bf 5 Poland

1 3 3 5 5 5 5

1 3 5 5 5 5 5

 

 

Want:

 

1 1 5 6 Rick . USA

1 1 5 6 Rick 2 USA

1 1 2 3 Stan 2 UK

1 2 . . . . UK 

1 2 . . . 4 UK

1 2 . . Sarah 4  Spain

1 2 . . Sarah 4  Spain

1 2 . . Sarah 4  Spain

1 3 . . Bf . Poland

1 3 3 . Bf . Poland

1 3 3 . Bf . Poland

 

 

As always, I appreciate any assistance!

 

Cheers!

 

P

Super Contributor
Super Contributor
Posts: 266

Re: Carrying non-missing values down a by group for all numeric and character variables in arrays an

Is it the case the the only records that really matter in WANT are 

 

1 1 2 3 Stan 2 UK
1 2 . . Sarah 4  Spain
1 3 3 . Bf . Poland

??

 

Occasional Contributor
Posts: 16

Re: Carrying non-missing values down a by group for all numeric and character variables in arrays an

Unfortunately, no. I setup this little test data for demonstration purposes, to show the process I'm attempting. The actual dataset has about 1300 columns and millions of rows.


Ask a Question
Discussion stats
  • 2 replies
  • 90 views
  • 0 likes
  • 2 in conversation