topic Re: Filling the missing value in SAS Procedures

Filling the missing value

m1986MM — Fri, 21 Aug 2015 20:47:54 GMT

Hello everyone,

In the following table I want to fill the missing value of MSA with non-missing values of those observations whose CBSA values are equal, so that the column MSA_filled be created. I tried retain but it didn't work. Do I need to use coalesce?

I appreciate your suggestions.

CBSA	MSA	MSA_filled
111	.	45
111	45	45
111	.	45
111	.	45
222	67	67
222	.	67
333	89	89
333	.	89
333	89	89
555	88	88
555	88	88
555	.	88
555	.	88

Re: Filling the missing value

Reeza — Fri, 21 Aug 2015 20:59:18 GMT

Retain won't work on as there is already a field.

Sort descending on MSA, create a new variable that is retained and fill down from there.

Re: Filling the missing value

m1986MM — Fri, 21 Aug 2015 21:20:57 GMT

The problem is that if there isn't any non-missing value for a specific CBSA then the cell will be filled with the previous values, but I want it to be missing, like the table below.

CBSA	MSA	MSA_filled
333	89	89
333	.	89
333	89	89
555	88	88
555	88	88
555	.	88
555	.	88
222	.	.
222	.	.
111	.	45
111	45	45
111	.	45
111	.	45

Re: Filling the missing value

Reeza — Fri, 21 Aug 2015 21:23:35 GMT

Use By Group processing on CBSA - if first.CBSA then set it to missing

Re: Filling the missing value

Uditg_ucla — Fri, 21 Aug 2015 22:09:36 GMT

There are plenty of ways to achieve this. However, the one that seems most intuitive to me is the following:

*Create a master list;

data Master; set Table;

if missing(MSA) then delete;

run;

*Make sure Master has just one entry for each CBSA;

proc sort data=Master nodupkey; by CBSA; run;

*Create new table as needed;

proc sql;

create table Table2 as

select t1.*, t2.MSA as MSA_filled

from Table as t1

left join Master as t2

on t1.CBSA = t2.CBSA

;quit;

Re: Filling the missing value

Ksharp — Sat, 22 Aug 2015 06:34:17 GMT

How about :

data have;
input CBSA     MSA      ;
cards;
111     .     
111     45     
111     .     
111     .     
222     67     
222     .     
333     89     
333     .     
333     89     
555     88     
555     88
555     .
555     .
;
run;
data want; 
retain MSA_filled _CBSA;
 do until(not missing(MSA) or last.CBSA);
  set have;
  by CBSA;
 end;
 if CBSA ne _CBSA then call missing(MSA_filled);
 if not missing(MSA) then MSA_filled=MSA ;
 do until(not missing(MSA) or last.CBSA);
  set have;
  by CBSA;
  output;
 end;
_CBSA=CBSA;
drop _CBSA;
run;

Xia Keshan

Re: Filling the missing value

Haikuo — Sat, 22 Aug 2015 19:26:43 GMT

Actually IMHO the simplest approach would be like this,

proc sql;

create table want as

select cbsa, max(msa,max(msa)) /*min(msa)*/ as msa

from have

group by cbsa;

quit;

min or max doesn't matter since they both ignore missing values.

Re: Filling the missing value

m1986MM — Sat, 22 Aug 2015 21:13:26 GMT

So, what's the point of using retain?

Re: Filling the missing value

Ksharp — Sun, 23 Aug 2015 03:48:23 GMT

If there is only one unique MSA for each group . You can do this .

Code: Program

data have;
input CBSA   MSA   ;
cards;
111 .  
111 45  
111 .  
111 .  
222 67  
222 .  
333 89  
333 .  
333 89  
555 88  
555 88
555 .
555 .
;
run;
data want;
 merge have have(keep=CBSA MSA rename=(MSA=MSA_filled) where=(MSA_filled is not missing));
 by CBSA;
run;

Re: Filling the missing value

Haikuo — Sun, 23 Aug 2015 16:46:43 GMT

I don't quite understand your question. 'RETAIN' is a concept for Date Step, where data is processed Sequentially. SQL does NOT use/need 'RETAIN'. As you may wonder in my code: max(msa, max(msa)), the second max() is the summary function to get the max of the group, it can be replaced by min(), which is also a summary function. The first max() is not a summary function, it is to compare msa of certain row with the group max, it can also be replaced by min() and coalesce().

Re: Filling the missing value

m1986MM — Sun, 23 Aug 2015 16:50:57 GMT

Thank you. Actually I wanted to know what's the main difference between SQL code and RETAIN. It seems that in my case both methods work.

CBSA	MSA	MSA_filled
111	.	45
111	45	45
111	.	45
111	.	45
222	67	67
222	.	67
333	89	89
333	.	89
333	89	89
555	88	88
555	88	88
555	.	88
555	.	88

CBSA	MSA	MSA_filled
111	.	45
111	45	45
111	.	45
111	.	45
222	67	67
222	.	67
333	89	89
333	.	89
333	89	89
555	88	88
555	88	88
555	.	88
555	.	88

CBSA	MSA	MSA_filled
111	.	45
111	45	45
111	.	45
111	.	45
222	67	67
222	.	67
333	89	89
333	.	89
333	89	89
555	88	88
555	88	88
555	.	88
555	.	88