topic How to separate data that have some distinct fields, but other fields that are unique? in New SAS User

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

helloagainoh2 — Wed, 24 Feb 2021 16:28:49 GMT

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.

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

ballardw — Wed, 24 Feb 2021 16:38:16 GMT

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

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

SASJedi — Wed, 24 Feb 2021 16:44:28 GMT

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;