Combining records into one observation - then arrange variable values based on another variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Combining records into one observation - then arrange variable values based on another variable

Hello:

 

I am working with joint account data and attempting to combine 2 records into one observation based on a variable (classnum). My biggest headache is arranging a primary and secondary account holder based on the value in another variable (flag). If flag=y then name1 is assigned as per the record with flag=y. If flag is missing it does not matter how the assignment is performed. As well there will be single account holders. In this case the name1 and accountnum1 fields are always populated but name2/accountnum2 will be blank.

 

My "before" data looks like this. The first 2 records with classnum=456 indicates Bob as the primary account holder and Mary as the secondary holder based on the flag value:


classnum       name        accountnum        flag
456                 Bob             777                   Y
456                 Mary           888                   N
333                 Sue            123                    Y
333                 Bill              444                   N
222                Joe              999
222               Harry            333
111               Sally             676                   Y

 


The "after" processing should appear like this:

 

classnum            name1                  name2               accountnum1               accountnum2
456                         Bob                    Mary                       777                             888
333                        Sue                     Bill                           123                             444
222                        Joe                     Harry                       999                              333
111                       Sally                                                    676

 

Many thanks for your assistance!


Accepted Solutions
Solution
‎11-09-2015 03:10 PM
Super User
Posts: 5,513

Re: Combining records into one observation - then arrange variable values based on another variable

There are a couple of details that you might want to think about ... can FLAG take on any other values besides Y, N, and blank?  Is it always uppercase?  Could there be more than 2 account holders?  At any rate, this would be a reasonable approach:

 

proc sort data=have;

by classnum descending flag;

run;

 

data want;

set have;

by classnum;

if first.classnum then do;

   name1 = name;

   accountnum1 = accountnum;

end;

else do;

   name2 = name;

   accountnum2 = accountnum;

end;

retain name1 account1;

if last.classnum;

run;

 

By sorting the "Y" values in descending order, the first record per CLASSNUM will be the "Y" (if there is a "Y" for that classnum).

 

Good luck.

View solution in original post


All Replies
Solution
‎11-09-2015 03:10 PM
Super User
Posts: 5,513

Re: Combining records into one observation - then arrange variable values based on another variable

There are a couple of details that you might want to think about ... can FLAG take on any other values besides Y, N, and blank?  Is it always uppercase?  Could there be more than 2 account holders?  At any rate, this would be a reasonable approach:

 

proc sort data=have;

by classnum descending flag;

run;

 

data want;

set have;

by classnum;

if first.classnum then do;

   name1 = name;

   accountnum1 = accountnum;

end;

else do;

   name2 = name;

   accountnum2 = accountnum;

end;

retain name1 account1;

if last.classnum;

run;

 

By sorting the "Y" values in descending order, the first record per CLASSNUM will be the "Y" (if there is a "Y" for that classnum).

 

Good luck.

Valued Guide
Posts: 860

Re: Combining records into one observation - then arrange variable values based on another variable

I think this will work for you, if you have a large population I would double check the output:

 

data have;
infile cards dsd;
input classnum$ name$ accountnum$ flag$;
cards;
456,Bob,777,Y
456,Mary,888,N
333,Sue,123,Y
333,Bill,444,N
222,Joe,999,
222,Harry,333,
111,Sally,676,Y
;

proc sql;
create table prep as
select distinct
a.classnum,
a.name as name1,
b.name as name2,
a.accountnum as accountnum1,
b.accountnum as accountnum2,
a.flag
from have a left join
     have b on
a.classnum = b.classnum and
a.name ne b.name
where a.flag = 'Y' or missing(a.flag);

proc sort data=prep out=want nodupkey;by descending classnum;

Occasional Contributor
Posts: 9

Re: Combining records into one observation - then arrange variable values based on another variable

Posted in reply to Steelers_In_DC

Both of you guys are life savers!  You saved me a tremendous amount of grief!  Keep up the good work.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 253 views
  • 0 likes
  • 3 in conversation