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

PhilG — Wed, 28 Feb 2018 00:12:43 GMT

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 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 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 3 . . Bf . Poland

1 3 3 . Bf . Poland

As always, I appreciate any assistance!

Cheers!

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

HB — Wed, 28 Feb 2018 17:33:08 GMT

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

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

PhilG — Wed, 28 Feb 2018 18:03:55 GMT

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.

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

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

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

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