BookmarkSubscribeRSS Feed
Nilani
Fluorite | Level 6

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.
7 REPLIES 7
Amir
PROC Star

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
Fluorite | Level 6

Thanks Amir. I just updated the question

 

Patrick
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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

Nilani
Fluorite | Level 6

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

Patrick
Opal | Level 21

@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)

Patrick_0-1699413560876.png

 

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

Patrick_1-1699413769983.png

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

 

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1324 views
  • 2 likes
  • 3 in conversation