topic Re: Filling empty cell with others cells of the same column in SAS Programming

Filling empty cell with others cells of the same column

ElodieT — Fri, 30 Jul 2021 21:15:34 GMT

Hello,

I would like to complete de empty cells of column Email with those already existing if they have the same name and stay in the same state.

I tried with If then do to create another column and fill it out, but it doesn't work.

Please, is there a way to do it?

Re: Filling empty cell with others cells of the same column

PaigeMiller — Fri, 30 Jul 2021 21:27:56 GMT

In the future, do not provide your data as a screen capture, because we can't work with screen captures and provide tested code. Instead, please provide data as SAS data step code, instructions are here: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ and then we can actually use your data and create tested code.

Here is a possible solution (UNTESTED)

proc sql;
    create table has_email as select * from have where not missing(email);
    create table want as select a.*,coalescec(a.email,b.email) from have a left join has_email b
         on a.full_name=b.full_name;
quit;

Re: Filling empty cell with others cells of the same column

mkeintz — Fri, 30 Jul 2021 22:20:43 GMT

And what if a given name/state combination has more than one non-blank email value?

Re: Filling empty cell with others cells of the same column

Patrick — Sat, 31 Jul 2021 01:41:24 GMT

Below one way to go (in case of multiple email addresses will only pick one per name and state).

data have;
  infile datalines truncover dsd;
  input (name state email) ($);
  datalines;
name1,state1,email1
name1,state2,
name1,state1,
name1,state2,email2
;

proc sort data=have out=have_sorted;
  by name state DESCENDING email ;
run;

data want;
  set have_sorted;
  by name state;
  retain r_email;
  if first.state then r_email=email;
  else email=r_email;
  drop r_email;
run;

Re: Filling empty cell with others cells of the same column

mkeintz — Sat, 31 Jul 2021 20:21:05 GMT

This code builds a lookup table in a hash memory-resident object. Then it uses it to populate missing emails for a given full_name/state.

It's untested in the absence of sample data in the form of a working data step:

data want (drop=_:);
  set have;
  if _n_=1 then do;
    declare hash h (dataset:'have (where=(not missing(email)) keep=full_name state email)');
      h.definekey('full_name','state');
      h.definedata('email');
      h.definedone();
  end;
  if email=' ' then _rc=h.find();
run;

If a given FULL_NAME/STATE has multiple observations with non-blank emails, this code retains the first one in the hash object (default behavior of the hash object).