CODEWITHSUNDEEP
sas
Jebediah15
Jebediah15

Reputation: 794

Use COALESCEC to fill missing values

Would someone confirm that the COALESCE or COALESCEC function is the correct command to use for the following?

I would like to assign a matching value from a non-missing corresponding observation to that of a missing observation.

So, if data looks like:

 Order  |   ID  
 apple     101xa  
 orange    201xb
 cherry    301xc
 apple       .

I will end up with:

 Taxo  |   Specialty2  
 apple     101xa  
 orange    201xb
 cherry    301xc
 apple     101xa

Thanks for the support!

EDIT: Current Code used--

proc sort data=fill; 
    by taxo descending specialty2;
run;

data fill_input;
    set fill;
        retain tempspecialty2;
        length tempspecialty2 $5;
            by taxo;
                if first.taxo then tempspecialty2=' ';
                if not missing(specialty2) then tempspecialty2=specialty2;
                else tempspecialty2=specialty2;
run;

proc freq data=fill_input;
    table tempspecialty2;
run;

Upvotes: 0

Views: 1921

Answers (1)

Joe
Joe

Reputation: 63424

Data step functions don't quite work that way; crossing the row boundary requires you to save the value in a temporary variable.

What you would need to do would be to sort by order. Then you can save the value of ID in a temporary retained variable, and use it if needed.

proc sort data=have; 
by order;
run;

data want;
set have;
retain tempID;
length tempID $5;
by order;
if first.order then tempID=' ';
if not missing(id) then tempID=id;
else id=tempID;
run;

If you cannot change the order of rows, then you would need to solve this in a different way, using a hash table or a format or something similar.

Upvotes: 2

Related Questions