Help using Base SAS procedures

Group data and spread across.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Group data and spread across.

Hi All,

I need help with below case. Below is input [first_name,last_name,addr]

Tony;Greg;apt#711;

Tony;Greg;1276 pan ave;

Tony;Greg;TUCSON AZ 85705;

Tony;Greg;usa;

I need to group by first_name and Last_name and put address value per each record.

Output: [first_name,last_name,addr1,addr2,addr3,addr4,addr5,addr6]

Tony;Greg;apt#711;1276 pan ave;TUCSON AZ 85705;usa; ; ;

can anyone please help me how I can achieve this. 

Best Regards,


Accepted Solutions
Solution
‎06-13-2015 03:07 AM
Super User
Posts: 10,035

Re: Group data and spread across.

The simplest way is using proc means + idgroup .

Or MERGE skill . Me ,Matt, Arthur.T have talked about at this paper .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

Code: Program

data have;
infile cards truncover expandtabs;
input (Firstname Lastname NewAddr OldAddr) (: $40.);
cards;
Tony Greg apt#1276 apt#909
Tony Greg DRAGRAM DRAGRAM
Tony Greg TUCSON AZ 85705 TUCSON AZ 85705
Tony Greg USA USA
Ricky Pointing apt#711 300
Ricky Pointing Jugle ave East BOYLSTON AVE E
Ricky Pointing PHOENIX SEATTLE
Ricky Pointing AZ 85123 WA 98102
Ricky Pointing USA USA
arjuna ranatunga 100 MAIN ST apt#1276
arjuna ranatunga PO BOX 1022 100 Islington rd west
arjuna ranatunga SEATTLE WA 98104 Seattle
arjuna ranatunga USA WA 93102
arjuna ranatunga USA
;
run;
proc sql;
select max(n) into : n
  from (select count(*) as n from have group by Firstname,Lastname);
quit;
proc summary data=have nway;
class Firstname Lastname;
output out=want idgroup(out[&n] (NewAddr OldAddr)=);
run;

Xia Keshan

Message was edited by: xia keshan

View solution in original post


All Replies
Super User
Posts: 5,513

Re: Group data and spread across.

Assuming your data are sorted:

proc transpose data=have prefix='addr' out=want;

    by first_name last_name;

    var addr;

run;

Occasional Contributor
Posts: 18

Re: Group data and spread across.

Posted in reply to Astounding

Hi,

Yes, I tried this. But I need 6 variables for address.

In the example, there are only 4 occurance; therefore, addr5, addr6 should be blank.

Best Regards,

Occasional Contributor
Posts: 18

Re: Group data and spread across.

any help.

Best Regards,

Super User
Posts: 19,822

Re: Group data and spread across.

You need to provide better sample data and sample required output. Please also include some more details in your question.

Occasional Contributor
Posts: 18

Re: Group data and spread across.

Reeza wrote:

You need to provide better sample data and sample required output. Please also include some more details in your question.

Sorry, I will try to be more clear.

I have input data as shown below. I need to group based on first and last name and populate address(s) across [horizontal] like shown below [max 6 address variables each].

Input:

First nameLast nameNew AddrOld Addr
TonyGregapt#1276apt#909
TonyGregDRAGRAMDRAGRAM
TonyGregTUCSON AZ 85705TUCSON AZ 85705
TonyGregUSAUSA
RickyPointingapt#711300
RickyPointingJugle ave EastBOYLSTON AVE E
RickyPointingPHOENIXSEATTLE
RickyPointingAZ 85123WA 98102
RickyPointingUSAUSA
arjunaranatunga100 MAIN STapt#1276
arjunaranatungaPO BOX 1022100 Islington rd west
arjunaranatungaSEATTLE WA 98104Seattle
arjunaranatungaUSAWA 93102
arjunaranatungaUSA

Output:

First nameLast nameNew addr1New addr2New addr3New addr4New addr5New addr6Old addr1Old addr2Old addr3Old addr4Old addr5Old addr6
TonyGregapt#1276DRAGRAMTUCSON AZ 85705USAapt#909DRAGRAMTUCSON AZ 85705USA
RickyPointingapt#711Jugle ave EastPHOENIXAZ 85123USA300BOYLSTON AVE ESEATTLEWA 98102USA
arjunaranatunga100 MAIN STPO BOX 1022SEATTLE WA 98104USAapt#1276100 Islington rd westSeattleWA 93102USA

Best Regards,

Solution
‎06-13-2015 03:07 AM
Super User
Posts: 10,035

Re: Group data and spread across.

The simplest way is using proc means + idgroup .

Or MERGE skill . Me ,Matt, Arthur.T have talked about at this paper .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

Code: Program

data have;
infile cards truncover expandtabs;
input (Firstname Lastname NewAddr OldAddr) (: $40.);
cards;
Tony Greg apt#1276 apt#909
Tony Greg DRAGRAM DRAGRAM
Tony Greg TUCSON AZ 85705 TUCSON AZ 85705
Tony Greg USA USA
Ricky Pointing apt#711 300
Ricky Pointing Jugle ave East BOYLSTON AVE E
Ricky Pointing PHOENIX SEATTLE
Ricky Pointing AZ 85123 WA 98102
Ricky Pointing USA USA
arjuna ranatunga 100 MAIN ST apt#1276
arjuna ranatunga PO BOX 1022 100 Islington rd west
arjuna ranatunga SEATTLE WA 98104 Seattle
arjuna ranatunga USA WA 93102
arjuna ranatunga USA
;
run;
proc sql;
select max(n) into : n
  from (select count(*) as n from have group by Firstname,Lastname);
quit;
proc summary data=have nway;
class Firstname Lastname;
output out=want idgroup(out[&n] (NewAddr OldAddr)=);
run;

Xia Keshan

Message was edited by: xia keshan

Occasional Contributor
Posts: 18

Re: Group data and spread across.

Hi Xia,

Thank you. I used Transpose + merge and it worked as expected.

Best Regards,

Super User
Posts: 5,513

Re: Group data and spread across.

PROC TRANSPOSE will create as many variables are are actually needed by your data.  If you are only getting four address variables, it's because that is as many as is required to transpose your data.  You can easily convert that to six variables with two blank ones.  Operating on the output of the PROC TRANSPOSE:

data want;

   set want;

   length addr5 addr6 $ 100;

run;

Pick whatever length you feel is appropriate for the extra two variables.

Occasional Contributor
Posts: 18

Re: Group data and spread across.

Posted in reply to Astounding

Hi Astounding,

But it is not always that first 4 records will have address.

There is a case where address is only one record i.e. addr1 has entire data and addr2 thru addr6 should be space.

There is a case where address is in 6 records i.e. addr1 thru addr6 has value [complete address].

Best Regards,

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 475 views
  • 6 likes
  • 4 in conversation