topic Re: Update missing values from with in Table in SAS Programming

Update missing values from with in Table

AliJ — Wed, 22 May 2013 08:21:36 GMT

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
A	1
	1
A
B	2
	2
B

As in ROW1 values are available for both coulmn, i want to update missing values in ROW2 & ROW3 , please guide How ?

Re: Update missing values from with in Table

Patrick — Wed, 22 May 2013 10:16:31 GMT

Look up the "lag()" function and "SAS by processing" (first and last);

Re: Update missing values from with in Table

Haikuo — Wed, 22 May 2013 13:15:59 GMT

Question couldn't more vague. "using value of adjacent coulmn as refernce"

1. I assume it is actually adjacent rows you are looking at, not column, from your context. But I could be wrong.

2. Adjacent where? which direction? from top down or from bottom up?

3. Reference how? how many values you take into consideration? straight copy or do something else, such as mean?

Haikuo

Re: Update missing values from with in Table

Astounding — Wed, 22 May 2013 13:16:20 GMT

Perhaps simplest:

data want;

set have;

if column1 > ' ' then last_column1 = column1;

else column1 = last_column1;

retain last_column1;

if column2 > . then last_column2 = column2;

else column2 = last_column2;

retain last_column2;

drop last_column1 last_column2;

run;

I'm hoping that this "simplest" approach is actually what you are looking for ... each variable can operate independently of the other. So if column2 is missing for the first observation having column1=B, it is OK to use a column2 value from column1=A.

Good luck.

Re: Update missing values from with in Table

Tal — Wed, 22 May 2013 13:31:24 GMT

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?

data one;

input Cou1 $ Cou2;

cards;

A 1

. 1

A .

B 2

. 2

B .

;

data two;

set one;

by cou1 notsorted;

lag_cou1=lag(cou1);

lag_cou2=lag(cou2);

if not(first.cou1) then do;

if cou1=' ' then cou1=lag_cou1;

if cou2=. then cou2=lag_cou2; end;

proc print;run;

Re: Update missing values from with in Table

Astounding — Wed, 22 May 2013 14:29:21 GMT

When you encounter a missing value for COU1, you are also encountering FIRST.COU1=1. So your IF THEN group will not execute.

Re: Update missing values from with in Table

data_null__ — Wed, 22 May 2013 14:46:51 GMT

Lag is not appropiate for this problem. But there are easy ways to get the desired result.

data a;
   input C1 :$1. C2;
   cards; 
A    1
.    1
A    .
B    2
.    2 
B    .
;;;;
   run; 
data av/ view=av;
   set a;
   retain dummyby 1; 
   run; 
data coalesced;
   update av(obs=0) av;
   by dummyby;
   output; 
   drop dummyby;
   run; 

;

Re: Update missing values from with in Table

Haikuo — Wed, 22 May 2013 14:55:08 GMT

Slick! There are also ways get it simply done in one step, inspired by the name you used "coalesced",

data a;

input C1 :$1. C2;

cards;

A 1

. 1

A .

B 2

. 2

B .

;;;;

data b ;

set a;

retain _1 ' ';

retain _2;

c1=coalescec(c1,_1);

c2=coalesce(c2,_2);

_1=c1;_2=c2;

drop _:;

run;

Haikuo

Re: Update missing values from with in Table

Tal — Wed, 22 May 2013 14:55:39 GMT

but cou1 is never missing when first.cou1=1.At least that what the table above shows?

Re: Update missing values from with in Table

data_null__ — Wed, 22 May 2013 15:04:01 GMT

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.

Re: Update missing values from with in Table

Haikuo — Wed, 22 May 2013 15:08:16 GMT

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!

Haikuo

Re: Update missing values from with in Table

Tal — Wed, 22 May 2013 15:18:27 GMT

sorry,what does this line below exactly do?

update av(obs=0) av;

Re: Update missing values from with in Table

data_null__ — Wed, 22 May 2013 16:05:36 GMT

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).

Re: Update missing values from with in Table

Tal — Wed, 22 May 2013 17:28:39 GMT

the av(obs=0) was actually confusing me.Merging a table with rows with a table with no rows and still achieving the results

Re: Update missing values from with in Table

data_null__ — Wed, 22 May 2013 19:29:12 GMT

It's not merge. The transactions are "overlayed" with the missing check not allowing a missing value to update a nonmissing value.

Re: Update missing values from with in Table

Tal — Fri, 24 May 2013 03:31:11 GMT

i thought the reason you created dummyby was to merge the 2 sets

Re: Update missing values from with in Table

Tom — Fri, 24 May 2013 04:43:38 GMT

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".

Re: Update missing values from with in Table

Tal — Sat, 25 May 2013 03:36:50 GMT

Thanks guys