populate other row if missing value

[podarum]

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

[AncaTilea]

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

[ChrisNZ]

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

[Ksharp]

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