BookmarkSubscribeRSS Feed
helloagainoh2
Calcite | Level 5

I have a data set in that is in this format

 

Name:       Email_type    Email

A                Business       xyz@blank

A                Personal       abc@blank

B                Business      123@blank

B               Personal        456@blank

 

The goal is to get it in this format,

 

Name     Personal_Email Business_email 

A           abc@blank           xyz@blank

 

I was thinking of sub setting the first data set with a where email_type = "Business" and then merging the two data sets using Name as a key. But I am wondering if there is a more effective way of dealing with this type of situation, as it shows up frequently.

 

 

2 REPLIES 2
ballardw
Super User

@helloagainoh2 wrote:

I have a data set in that is in this format

 

Name:       Email_type    Email

A                Business       xyz@blank

A                Personal       abc@blank

B                Business      123@blank

B               Personal        456@blank

 

The goal is to get it in this format,

 

Name     Personal_Email Business_email 

A           abc@blank           xyz@blank

 

I was thinking of sub setting the first data set with a where email_type = "Business" and then merging the two data sets using Name as a key. But I am wondering if there is a more effective way of dealing with this type of situation, as it shows up frequently.

 

 


If you only have one value for each of the email_types per id (only one personal and/or only one business) then this may help:

 

Proc sort data=have;
   by id;
run;

Proc transpose data=have
        out=want prefix=email;
   by id;
   id email_type;
   var email;
run;

This will create a new variable with the name of "email" as prefix and the value of email_type as the rest of the name.

 

If there are other variables that need to be considered that is another story as we would need a more complete example of the data and the desired result.

SASJedi
SAS Super FREQ

If things get more complicated, you could do a controlled transposition with a DATA step, like this:

data have;
   infile datalines truncover;
   input Name:$1. Email_type:$8.   Email:$20.;
datalines;
A Business xyz@blank
A Personal abc@blank
B Business 123@blank
B Personal 456@blank
;

data want;
   set have;
   by name;
   length Personal_Email Business_Email $20;
   retain Personal_Email Business_Email ;
   if first.name then do;
      call missing(Personal_Email, Business_Email);
   end; 
   if email_type="Business" then Business_Email=Email;
   else if email_type="Personal" then Personal_Email=Email;
   if last.name then output;
   drop Email:;
run;
Check out my Jedi SAS Tricks for SAS Users

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 469 views
  • 3 likes
  • 3 in conversation