05-22-2013 04:21 AM
I have to update missing values in a in both coulmns using value of adjacent coulmn as refernce for search. refer to below example
|Coulmn 1||Coulmn 2|
As in ROW1 values are available for both coulmn, i want to update missing values in ROW2 & ROW3 , please guide How ?
05-22-2013 09:31 AM
assuming that the first record of each by group is never missing i did this coding and still the table does not update.you know why,just curious?
input Cou1 $ Cou2;
by cou1 notsorted;
if not(first.cou1) then do;
if cou1=' ' then cou1=lag_cou1;
if cou2=. then cou2=lag_cou2; end;
05-22-2013 10:46 AM
Lag is not appropiate for this problem. But there are easy ways to get the desired result.
05-22-2013 10:55 AM
Slick! There are also ways get it simply done in one step, inspired by the name you used "coalesced",
input C1 :$1. C2;
data b ;
retain _1 ' ';
05-22-2013 11:04 AM
Yes but you to write more code: rename and assignments, etc. The MISSING CHECK feature of the UPDATE is a powerfull alternative applied to all the variables.
05-22-2013 11:08 AM
Yes, DN! And I have to use Array() if more variables get involved, the code will be increasingly bulky. I was thinking of using Update, but didn't go far enough to introduce a dummy id variable. Another trick I learned from you!
05-22-2013 12:05 PM
Have you looked at the documentation regarding the UPDATE statement?
WRT the line in question, it reads observations from the transaction data using the UPDATE statement. The UPDATE statement requires exactly two data sets as input a master and transaction. This technique uses a dummy master av(obs=0).
05-24-2013 12:43 AM
The BY statement is required when using an UPDATE statement so that the, potentially multiple, transactions can be assigned to the proper record in the master. You are allowed to add new records to the MASTER via UPDATE, hence the use of a MASTER table with no observations.
Data _null_'s twist on the standard UPDATE that makes it work for this type of Last Observation Carried Forward application is to add the OUTPUT statement. So rather than collapsing the whole dataset into one row per BY group the data step emits an observation after each record read from the transaction table. So we end up with the same number of observations as we started with.
Missing values in the transaction table do NOT overwrite the values already in the variables, hence the previous value is "carried forward".