topic Re: Filling in missing Values in SAS Programming

Filling in missing Values

RandyStan — Mon, 19 Oct 2020 20:02:14 GMT

Dear All

My Data set looks like this:

ID VAR_A

1 A

2 .

2 A

3 .

4 C

5 D

I want it to look like this:

ID VAR_A Correct_VAR_A

1 A A

2 . A

2 A A

3 .

4 C C

5 D

5 D D

5 D

Please help

Randy

Re: Filling in missing Values

Reeza — Mon, 19 Oct 2020 20:06:55 GMT

If you can always guarantee that you only have one value of VAR_A per ID use SQL.

proc sql;
create table want as select *, max(var_a) as correct_var_a from have group by ID order by ID;
quit;

Re: Filling in missing Values

novinosrin — Mon, 19 Oct 2020 20:15:35 GMT

Just one unique Var_A values per ID? Just can't think of a scenario

Anyway-


data have;
input ID                VAR_A $;
cards;
1                    A

1                   A

1                   A

2                   .

2                   A

2                   A

3                   .

3                   .

3                   .

4                  C

4                  C

5                  .

5                   D

5                   .
;

data want;
 merge have(drop=var_a) have(where=(var_A>' '));
 by id;
run;

Re: Filling in missing Values

RandyStan — Mon, 19 Oct 2020 20:21:50 GMT

Yes there is only one unique value per ID

I looked up this solution. But it is much more complicated than the one posted by novinosrin

data have;
input ID    status;
cards;
1        . 
1        .
1        . 
1        5
2        .
2        .
2        6
;
run;

data want;
 do until(not missing(status) or last.id );
  set have;
  by id;  
 end;
 temp=status;
 do until(not missing(status) or last.id );
  set have;
  by id;
  _status=temp;output;
 end;
 drop temp status;
run;

Re: Filling in missing Values

novinosrin — Mon, 19 Oct 2020 20:23:50 GMT

I honestly can't see a need for a double DOW loop i.e. assuming I understood the requirement. Am i missing something

Re: Filling in missing Values

RandyStan — Mon, 19 Oct 2020 21:11:03 GMT

Novinosrin:

I agree with your last statement. I shall test your code later tonight.

Thanks so much.