How to separate data that have some distinct fields, but other fields that are unique?

[helloagainoh2]

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.

[ballardw]

---

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

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;