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;
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.
Thanks Amir. I just updated the question
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;
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;
@Nilani Using a hash table approach would avoid the need to sort the source table.
Thanks for the Tip. I am a beginner user. Couldn't understand your code.
@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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: