SAS Help Please!: Data manipulation question

[kushiwije]

Hi,
I have a data set as follows:

id x1 x2 x3 x4 x5 x6 x7 x8 no. of non-missing vars
-- -- -- -- -- -- -- -- -- -----------------------
1 . 3 4 . . . . . 2
2 3 . . 5 . . 9 . 3
3 . 4 8 5 4 . 9 7 6

I know that in each row, that the max no. of non-missing
variables is 6. I would like to re-sequence the values
of x1-x8 in to var1 thru var6 for each row. So the resulting data set would look like as follows:

id x1 x2 x3 x4 x5 x6 x7 x8 | var1 var2 var3 var4 var5 var6
1 . 3 4 . . . . . | 3 4 . . . .
2 3 . . 5 . . 9 . | 3 5 9 . . .
3 . 4 8 5 4 . 9 7 | 4 8 5 4 9 7

I would appreciate if someone would tell me the most efficient way to do this. Infact, instead of x1 thru x8, I actually have x1 thru x2000 (2000 variables) but still the maximum no. of non-missing variables is 6 for each record. I have about a million such records in my data set.

Thanks very much.
Kushi

 

[ChrisW75]

Tricky.
I think the best way, from a programming point of view is to load the values into an array, then loop through them loading non-missing values into a second array.

An alternative method would be to concatenate them as a string, separated by spaces. Then use COMPBL to remove duplicate spaces. you'll end up with a single string containing the 6 values separated by a single space. Then split them out again using the SCAN function.

ARRAYs are the better option though, more robust and straightforward.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.