Alternative to transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Alternative to transpose

Hi,

I have a dataset that looks like the following:

  

Client NumberlinkedValueName

1

85John
174Jim
135Bob
294Kelly
292Ann
219Karen

I would like to end up with:

Client NumberHeader 2Header 3Header 4Header 5Header 6Header 7
185John74Jim35Bob
294Kelly92Ann19Karen

I attempted to use transpose through EG, but it's not returning the desired outcome. I also searched and unless I missed it, I cannot find a suitable answer in previous threads.

Any help or direction would be appreciated


Accepted Solutions
Solution
‎10-11-2013 05:54 PM
PROC Star
Posts: 7,489

Re: Alternative to transpose

Posted in reply to FraudReporter

Since that is "exactly what the want should be", I'll recommend that you use the macro that some colleagues and I wrote and presented at this year's SAS Global Forum.  The code and paper can be found at: A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

Interestingly, yours is the 2nd post in the past two days that I thought would benefit from using the macro.  The first poster tried it and discovered that did indeed accomplish the desired task.

Once you download and run the macro, I think that running the following code will produce the output file you want.  The only things you may have to change are the data and out parameters to specify the input data and the output file.  In the call to the macro, shown below, they are work.have and work.want, respectively.

%transpose(data=work.have, out=work.want,

           var=CLNT_NO2 NM_GIVEN  NM_MID NM_SURNM,

           by=CLNT_NO1,

           copy=CLNT_ORG_NM CNTCT_OFFCER ADDR_ID

                ADDR_LIN_1 ADDR_LIN_2 CTY_TWN_NM

                CNTRY_CD PSTL_ZIP_CD SUBCNTRY_CD,

           delimiter=_,

           sort=yes)

Art

View solution in original post


All Replies
Super User
Posts: 19,855

Re: Alternative to transpose

Posted in reply to FraudReporter

In EG or BASE you'd need to transpose twice, once for each variable and then merge the two datasets together.

You can also do a datastep transpose if you wanted:

SAS Learning Module: Reshaping data long to wide using the data step

See example 2, reshaping two variables.

Trusted Advisor
Posts: 1,137

Re: Alternative to transpose

Posted in reply to FraudReporter

Hi,

Please find below an alternative to transpose (arrays) code, hope this helps.

data have;

    input id num name$;

cards;

1    85    John

1    74    Jim

1    35    Bob

2    94    Kelly

2    92    Ann

2    19    Karen

;

run;

proc sort data=have;

    by id;

run;

data have_;

    set have;

    by id;

    retain seq;

    if first.id then seq=1;

    else seq+1;

run;

data want;

    set have_;

    by id ;

    retain header1-header6;

    array nums(3) header1 header3 header5;

    array cha(3) $ header2 header4 header6;

    if first.id then do i = 1 to 3;

    nums(i)=.;

    cha(i)='';

    end;

    nums(seq)=num;

    cha(seq)=name;

    if last.id;

    drop num name i seq;

run;

Thanks,

Jagadish

Thanks,
Jag
Occasional Contributor
Posts: 9

Re: Alternative to transpose

Posted in reply to FraudReporter

Thank you for your help. looks like the right direction. I've been trying to adapt it to my whole dataset but it has not been successful. Somehow it is not capturing the proper data according to the main variable (the one I called id above). Maybe I should provide further info.

my have:

111111112ABC inc.Contact123 main streetTorontoCANX0X0X0ON222222223JohnPaulVAN MARREWYK
111111112ABC inc.Contact123 main streetTorontoCANX0X0X0ON333333334JimJayVAN MARREWYK
111111112ABC inc.Contact123 main streetTorontoCANX0X0X0ON444444445BobVAN MARREWYK
111111112ABC inc.Contact123 main streetTorontoCANX0X0X0ON555555556KellyVAN MARREWYK
111111112ABC inc.Contact123 main streetTorontoCANX0X0X0ON666666667AnnANDERSON

my want:

111111112ABC inc.Contact123 main streetTorontoCANX0X0X0ON222222223JohnPaulVAN MARREWYK333333334JimJayVAN MARREWYK444444445BobVAN MARREWYK555555556KellyVAN MARREWYK666666667AnnANDERSON

so far my code retains headers 76 headers as some of the entries can reach up to 19 records causing duplication... my data set is already sorted

data have_;

    set sample.bdainfo2;

    by clnt_no1;

    retain seq;

    if first.clnt_no1 then seq=1;

    else seq+1;

run;


data want;

    set have_;

    by clnt_no1 ;

    retain header1-header76;

    array nums(19) header1 header5 header9 header13 header17 header21 header25 header29 header33 header37 header41 header45 header49 header53
     header57 header61 header65 header69 header73;

    array cha(57) $ header2 header3 header4 header6 header7 header8 header10 header11 header12 header14 header15 header16 header18
header19 header20 header22 header23 header24 header26 header27 header28 header30 header31 header32 header34 header35 header36
header38 header39 header40 header42 header43 header44 header46 header47 header48 header50 header51 header52 header54 header55 header56
header58 header59 header60 header62 header63 header64 header66 header67 header68 header70 header71 header72 header74 header75 header76;

    if first.clnt_no1 then do i = 1 to 14;

    nums(i)=.;

    cha(i)='';

    end;

    nums(seq)=clnt_no2;

    cha(seq)=NM_GIVEN;
cha(seq)=NM_MID;
cha(seq)=NM_SURNM;

    if last.clnt_no1;

    drop clnt_no2 NM_GIVEN NM_MID NM_SURNM i seq;

run;

Regular Contributor
Posts: 168

Re: Alternative to transpose

Posted in reply to FraudReporter

Hi FraudReporter;

if i understood correctly , here is another approach.little bit modified version of jagadish

data have;

input id : $9. address $10-65 num : $9. name  $75-100;

cards;

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON 222222223 John Paul VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON 333333334 Jim Jay VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON 444444445 Bob  VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON 555555556 Kelly  VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON 666666667 Ann

;

run;

data have;

set have;

by id;

seq+(-first.id*seq)+1;

run;

data want;

retain id address header1- header10;

do until(last.id);

set have;

array number(*)$20 header1 header3 header5 header7 header9;

array nam(*)$20 header2 header4 header6 header8 header10;

set have;

by id;

   number(seq)=num;

   nam(seq)=name;

end;

drop seq num name;

run;

Thanks

Sam

PROC Star
Posts: 7,489

Re: Alternative to transpose

Posted in reply to FraudReporter

Given your description it is awfully difficult to figure out either what you have or what you want.

I'm only showing the following data steps so that you can produce two files: work.have and work.want.

Let us know if work.have is close to your input dataset and if work.want is close to what you want to achieve:

data have;

  informat clnt_no1 $9.;

  informat company $10.;

  informat contact $10.;

  informat address $20.;

  informat city $15.;

  informat country $3.;

  informat postal_code $6.;

  informat province $2.;

  informat id $9.;

  informat first_name $15.;

  informat middle_name $15.;

  informat last_name $25.;

  input clnt_no1 company & contact address & city country postal_code province

        /id first_name middle_name last_name &;

  cards;

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

222222223 John Paul VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

333333334 Jim Jay VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

444444445 Bob . VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

555555556 Kelly . VAN MARREWYK

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

666666667 Ann .  ANDERSON

111111113 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

222222223 John Paul VAN MARREWYK

111111113 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

333333334 Jim Jay VAN MARREWYK

111111113 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

444444445 Bob . VAN MARREWYK

111111113 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

555555556 Kelly . VAN MARREWYK

111111113 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

666666667 Ann .  ANDERSON

;

data want;

  informat clnt_no1 $9.;

  informat company $10.;

  informat contact $10.;

  informat address $20.;

  informat city $15.;

  informat country $3.;

  informat postal_code $6.;

  informat province $2.;

  informat id1-id5 $9.;

  informat first_name1-first_name5 $15.;

  informat middle_name1-middle_name5 $15.;

  informat last_name1-last_name5 $25.;

  input clnt_no1 company & contact address & city country postal_code province

        /id1 first_name1 middle_name1 last_name1 &

        /id2 first_name2 middle_name2 last_name2 &

        /id3 first_name3 middle_name3 last_name3 &

        /id4 first_name4 middle_name4 last_name4 &

        /id5 first_name5 middle_name5 last_name5 &;

  cards;

111111112 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

222222223 John Paul VAN MARREWYK

333333334 Jim Jay VAN MARREWYK

444444445 Bob . VAN MARREWYK

555555556 Kelly . VAN MARREWYK

666666667 Ann .  ANDERSON

111111113 ABC inc. Contact 123 main street Toronto CAN X0X0X0 ON

222222223 John Paul VAN MARREWYK

333333334 Jim Jay VAN MARREWYK

444444445 Bob . VAN MARREWYK

555555556 Kelly . VAN MARREWYK

666666667 Ann .  ANDERSON

;

Occasional Contributor
Posts: 9

Re: Alternative to transpose

My apologies if I am not explaning myself correctly...  all of the above do the job but only partially. and I am having trouble adapting it to my dataset.

My dataset as illustrated below contains thousands of rows. I basically want to group all entries in clnt_no2, Nm_Given, Nm_MD, NM_Surnm and present them on the same record, while retaining all the other values.

Thank you all for your input!

BDAcolumns.JPG

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Alternative to transpose

Posted in reply to FraudReporter

Hi

I have used transpose (although you asked for alternative to transpose)


/*Alternative to transpose */

data have;
input Client_Number   linkedValue$ Name $;
cards;
1 85 John
1 74 Jim
1 35 Bob
2 94 Kelly
2 92 Ann
2 19 Karen

;
run;
proc sort data=have;
by Client_Number ;
run;

data have1;
set have;
by  Client_Number ;
retain id;
if first.Client_Number then id=1;
else id+1;
run;


data linkedValue;
set have1;
rename linkedValue=value;
drop Name;

data Name;
set have1;
rename Name=value;
drop linkedValue;

data all;
set linkedValue Name;
run;

proc sort data=all;
by Client_Number id;
run;

proc transpose data=all out=want(drop=_name_) prefix=Header ;
by Client_Number;
var value;
run;

output :


Client_Number


Header1


Header2


Header3


Header4


Header5


Header6


1


85


John


74


Jim


35


Bob


2


94


Kelly


92


Ann


19


Karen

PROC Star
Posts: 7,489

Re: Alternative to transpose

Posted in reply to FraudReporter

Now we know what your data look like, but apparently not what you want the revised file to look like.  The example I posted was asking you if you wanted to group all records that share the same CLNT_NO1 value into one record that had the following fields:

CLNT_NO1

CLNT_ORG_NM

CNTCT_OFFCER

ADDR_ID

ADDR_LIN_1

ADDR_LIN_2

CTY_TWN_NM

CNTRY_CD

PSTL_ZIP_CD

CLNT_NO2_1

NM_GIVEN_1

NM_MID_1

NM_SURNM_1

CLNT_NO2_2

NM_GIVEN_2

NM_MID_2

NM_SURNM_2

. . .

CLNT_NO2_n

NM_GIVEN_n

NM_MID_n

NM_SURNM_n

If that isn't what you want, you would have to indicate what you DO want.

Occasional Contributor
Posts: 9

Re: Alternative to transpose

That is exactly what the "want" should be

Thanks again!

Solution
‎10-11-2013 05:54 PM
PROC Star
Posts: 7,489

Re: Alternative to transpose

Posted in reply to FraudReporter

Since that is "exactly what the want should be", I'll recommend that you use the macro that some colleagues and I wrote and presented at this year's SAS Global Forum.  The code and paper can be found at: A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

Interestingly, yours is the 2nd post in the past two days that I thought would benefit from using the macro.  The first poster tried it and discovered that did indeed accomplish the desired task.

Once you download and run the macro, I think that running the following code will produce the output file you want.  The only things you may have to change are the data and out parameters to specify the input data and the output file.  In the call to the macro, shown below, they are work.have and work.want, respectively.

%transpose(data=work.have, out=work.want,

           var=CLNT_NO2 NM_GIVEN  NM_MID NM_SURNM,

           by=CLNT_NO1,

           copy=CLNT_ORG_NM CNTCT_OFFCER ADDR_ID

                ADDR_LIN_1 ADDR_LIN_2 CTY_TWN_NM

                CNTRY_CD PSTL_ZIP_CD SUBCNTRY_CD,

           delimiter=_,

           sort=yes)

Art

Occasional Contributor
Posts: 9

Re: Alternative to transpose

Thank you!

Looks like hitting the nail on the head, I'll sort through it.

Thank you all for your help.

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 431 views
  • 10 likes
  • 6 in conversation