SAS, replace missing column values with the column values from right

0
votes

I have a dataset which has 100 columns. Every time it will have some missing values for some columns (the whole column values missing). The locations are fixed so I cannot specify them by columnA=ColumnB.

What I want to do is to replace the whole missing columns values with the column values from the column on its right side (see test data). Thank you.

DATA have ;
LENGTH make $ 20 ;
INPUT make $ 1-17 a b c d m h u;
CARDS;
AMC Concord        4099 22 . 2  1   . 22 
AMC Pacer          4749 17 . 2  1   . 17
Audi 5000          9690 17 . 3  23  . 17
Audi Fox           6295 23 . 1  32  . 23
;
RUN;

DATA want;
LENGTH make $ 20 ;
INPUT make $ 1-17 a b c d m h u;
CARDS;
AMC Concord        4099 22 2 2  1  22 22  
AMC Pacer          4749 17 2 2  1  17 17
Audi 5000          9690 17 3 3  23 17 17
Audi Fox           6295 23 1 1  32 23 23;
RUN;
1
design-patternssas
What do you mean by 'the locations are fixed'? Do you need this to work without knowing the column names? What do you want to do if two or more consecutive columns have missing values? – user667489
Is it the case that every time there is a missing value you want to replace it with the value of the "next" variable (next being position / varnum)? Are all the variables numeric, as in your example? If so, you could use an array. But this is an odd requirement. I can't think of a time it's useful to have two variables with the exact same values. – Quentin
667489 - it means this time col 34 has missing values, next time, col 76 has missing values. If two or more consecutive columns have missing, ..we currently do not run into this situation so I do not know the answer. Hope at that time I can revise the sample code provided here. – user1481397
Quentin- All my values are numeric. This is an odd requirement. You are correct. The request is from business side. – user1481397

1 Answers

3
votes

Assuming you can't have missing without the whole column being missing:

data want;
set have;
array vars(*) a -- u;

do i = 1 to dim(vars)-1;
if vars(i) = . then vars(i)=vars(i+1);
end;

run;