topic Re: Fill a column based on condition in SAS Enterprise Guide

Fill a column based on condition

Nilani — Wed, 01 Nov 2023 01:42:51 GMT

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.

Re: Fill a column based on condition

Amir — Tue, 31 Oct 2023 22:19:23 GMT

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.

Re: Fill a column based on condition

Patrick — Tue, 31 Oct 2023 22:28:03 GMT

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;

Re: Fill a column based on condition

Nilani — Tue, 31 Oct 2023 22:23:33 GMT

Thanks Amir. I just updated the question

Re: Fill a column based on condition

Nilani — Wed, 01 Nov 2023 01:41:10 GMT

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;

Re: Fill a column based on condition

Patrick — Wed, 01 Nov 2023 04:45:13 GMT

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

Re: Fill a column based on condition

Nilani — Wed, 08 Nov 2023 01:21:03 GMT

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

Re: Fill a column based on condition

Patrick — Wed, 08 Nov 2023 03:27:12 GMT

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