topic Re: populate other row if missing value in SAS Procedures

populate other row if missing value

podarum — Mon, 17 Jun 2013 18:35:16 GMT

Hi,

If I have a field called FSA, how do I check if it has a value (eg. M5V) missing , then if it is not ther populate it with the information from another field eg FSA = M1. If it's there, then do nothing.

HAVE:

FSA Field1 Field2

M5 A 45

M5 B 45

M5 A 36

M7A A 98

M7 A 76

......

I want to check if the table has field M5V, if it doesn't then this is what I expect:

FSA Field1 Field2

M5V A 45

M5V B 45

M5V A 36

M5 A 45

M5 B 45

M5 A 36

M7A A 98

...

same test would be for other FSa's, not just M5V..

thanks..

M7 A 76

Re: populate other row if missing value

AncaTilea — Mon, 17 Jun 2013 18:59:18 GMT

Hi.

This is a poorly written code (since I find it a bit confusing at how you want to assign data if a certain value is missing):

*create data;

data have;

input FSA $ Field1 $ Field2;

cards;

M5 A 45

M5 B 45

M5 A 36

M7A A 98

M7 A 76

;

data test;

set have;

by fsa;

output;

flag = (fsa = "M5");

if fsa ne "M5V" & flag = 1 then do;

temp_fsa = "M5V";temp_field1 = field1;temp_field2 = field2;

end;

output;

run;

data want;

set test;

if temp_fsa ne "" then do;

fsa = temp_fsa;

field1 = temp_field1;

field2 = temp_field2;

end;

drop temp: flag;

run;

proc sort data = want nodupkey;by fsa field1 field2;run;

This could be further improved, and possibly a macro could be used to allow for various FSA values...

Re: populate other row if missing value

ChrisNZ — Tue, 18 Jun 2013 02:11:24 GMT

How do you know value M5V should even exist if it is not in the file?

Re: populate other row if missing value

Ksharp — Tue, 18 Jun 2013 09:23:12 GMT

OK. If I understand what you mean.

data have;
input FSA     $ Field1     $ Field2;
cards;
M5         A           45
M5         B           45
M5        A            36
M7A       A           98
M7         A           76
;
run;
data want(drop=rc _:);
 if _n_ eq 1 then do;
  declare hash ha(dataset:'have(where=(fsa="M5"))',multidata:'y');
   ha.definekey('fsa');
   ha.definedata(all:'y');
   ha.definedone();
  declare hash h();
   h.definekey('fsa');
   h.definedone();
end;
set have;
_fsa=fsa;_field1=field1;_field2=field2;
 if h.add() eq 0 then do;
                        rc=ha.find();
                              do while(rc=0);
                               fsa=cats(fsa,'V');
                               output;
                               fsa=substr(fsa,1,length(fsa)-1);
                               rc=ha.find_next();
                              end;
                            end;
fsa=_fsa;field1=_field1;field2=_field2;
output;
run;

Ksharp