I have the data as follows. Suppose it has 2 variables X and Y and the values are as below
and something like that....The missing value for variable X from row 2 to row 5 is A and the missing value for variable X in row 7 is B and the missing value for variable X from row 9 to 12 is C. What should I do to fill in the appropriate value in place of the missing values. The above dataset is just an example. In my actual data I have 89 different values for variable X and the number of missing values under it keep on changing.
Can someone please let me know what is an effective way to solve this problem ( the code has to be dynamic). Thanks in advance!
Using a DATA step and a DO UNTIL(EOF); with an INFILE and END=EOF coded, you can input the data record and detect when you have one or two field values. When you have one value, do not overlay "X", only overlap/replace "Y", and only perform your OUTPUT when you have the correct data/variable condition. Some functions you will consider using are SCAN and also INPUT when assigning your true "X" and "Y" variables. If you iterate your DATA step with the DO/END and having the INPUT within the code portion, you then do not need to use the RETAIN statement since SAS will not return to the top of the DATA step.
Suggest you track your program with some well-placed PUTLOG '>diag-nnn>' / _ALL_; commands within your DO / END programming loop.
Recommended Google advanced search arguments, this topic/post:
data step programming external data site:sas.com
data step parse external data input function site:sas.com
data step parse external data _infile_ site:sas.com
XorYisvoid= is for knowing that X or Y was void, but i didn't undestand the behavior of your data.
voidValue = for retaining the last value.
So you will need a do loop in scl, and some macrovariables. 89 variables is too much for programing with simple if.
The next code preteds to collect your variables names, and after using a do loop to fill 1 to 1 the variables, B to A, C to B ...
But i have a cuestion, the 89th var have to refill the other 88 if all are null? then you have to start from 89 to 1.
First of all you have to make a data step with variables, something like:
1 Name, (we call it A)
2 State, (we call it B)
do i=1 to dim (Vardata-1)
if _N_ eq &MyIndex Then call simput ('ValueThatIhaveToFill',VarName);
if _N_ eq &MyIndex+1 Then call simput ('ValueThatIhaveToPass',VarName);
If &ValueThatIhaveToFill eq '' then &ValueThatIhaveToFill (name, state) = &ValueThatIhaveToPass;
end; (do loop)
This is not tested but i think it's basically that you want.
Looks like data I sometimes get from EXCEL. I assume your starting point is a SAS data set.
input x $ y @@;
A 2 . 4 . 6 . 8 . 10
B 1 . 2
C 1 . 2 . 3 . 4
if 0 then set have(keep=x);
x = coalesceC(_x,x);
You can drop _X
Obs x _x y
1 A A 2
2 A 4
3 A 6
4 A 8
5 A 10
6 B B 1
7 B 2
8 C C 1
9 C 2
10 C 3
11 C 4