Fill a column based on condition

[Nilani]

Hi, I have a dataset like following.

data my_data;
    input ApplicationNumber  id  email $  applicant_type $;
    datalines;
1 10 a@b.com A
1 20  D
2 30 b@c.com A
2 40  D
2 50  D
3 60 d@e.com D
4 70 A

;
run;

I want to fill missing emails emails. If the applicant_type is "A" and email is missing do nothing. If the applicant type is "D" and missing email then, group by application number and get the email of applicant type "A" to that has missing email of applicant type "D".

Expected Output

1 10 a@b.com A

1 20 a@b.com D

2 30 b@c.com A

2 40 b@c.com D

2 50 b@c.com D

3 60 d@e.com D

4 70                   A

Thanks.

[Amir]

Hi,

Thanks for providing a data step for the input data, however as SAS was going to a new line due to missing data, I added an infile statement as shown below.

I had trouble understanding the method you wanted to follow, so please also provide a data step showing what output you expect for the given input, and if you need to cover more situations then please add them to your data steps.

data have;
  infile datalines truncover;
  input
    ApplicationNumber
    id
    email $
    applicant_type $
  ;
  
  format  carrier_date Date9.;
  
  datalines;
1 10 a@b.com A
1 20  D
2 30 b@c.com A
2 40  D
2 50  D
3 60 d@e.com D
4 70 A
;

Thanks & kind regards,

Amir.

[Nilani]

Thanks Amir. I just updated the question

[Patrick]

Please make it a habit to test the code that creates the sample data because this allows us to spend the time answering your question instead of fixing the sample data creation code.

Here you go:

data have;
  infile datalines dlm=' ' dsd truncover;
  input ApplicationNumber  id  email $  applicant_type $;
  datalines;
1 10 a@b.com A
1 20  D
2 30 b@c.com A
2 40  D
2 50  D
3 60 d@e.com D
4 70  A
;

data want;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'have(where=(applicant_type="A" and not missing(email))');
      h1.defineKey('ApplicationNumber');
      h1.defineData('email');
      h1.defineDone();
    end;

  set have;
  if applicant_type = 'D' and missing(email) then h1.find();
run;

[Nilani]

Thanks for the responses. The following code worked for me.

proc sort data=my_data;
by ApplicationNumber applicant_type;
run;

data want;
set my_data;
by ApplicationNumber;
retain filled_email;
if applicant_type = "A" and not missing (email) then
filled_email=email;
else if applicant_type = "D" and missing (email) then
email=filled_email;
output;
run;

[Patrick]

@Nilani Using a hash table approach would avoid the need to sort the source table.

[Nilani]

Thanks for the Tip. I am a beginner user. Couldn't understand your code. 

[Patrick]

---

@Nilani wrote:

Thanks for the Tip. I am a beginner user. Couldn't understand your code. 

---

A SAS hash table is a table in memory that you can use to lookup values. One of the advantages using a hash table is that it removes the need to sort your base table for such lookups.

Learning what SAS hash tables are and how to use them is something I'd recommend you put on your learning list.

Below code creates and loads the hash table. The where clause only loads rows where applicant_type="A" and not missing(email)

You then can use the hash find() method to lookup values over a key.

The find() method will use ApplicationNumber from your base table as key for the lookup and if there is a match will return the value(s) as defined as data - here: email.

It's pretty similar to using a Format or Informat for such lookup but with a hash table you can use multiple variables for key definition and you can also define multiple variable as data (=lookup multiple variables at once).