DATA Step, Macro, Functions and more

transpose

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

transpose

Hi, I have data like table 1. I need it to convert it like table 2. Can you help me do it?

Table 1

_TYPE__OBS_ZipcodeAreacodeTimezoneGMTDST
BASE400602787Atlantic-4N
COMPARE400602939Atlantic-4N
BASE1100612787Atlantic-4N
COMPARE1100612939Atlantic-4N
BASE1400616787Atlantic-4N
COMPARE1400616305Atlantic-4N
BASE3974996916671UTC+1010N
COMPARE3974996916671Pacific/14N
BASE3975096917671UTC+1010N
COMPARE3975096917671Pacific/14N
BASE3975896939671UTC+99N
COMPARE3975896939969Japan15Y
BASE3975996940680UTC+99N
COMPARE3975996940969Japan15Y

Table 2

ZipcodeBASE Area codeCOMPARE Area CodeBase TmeZoneCompare TimeZoneBase GMTCompare GMTBase DSTCompare DST
00602787939AtlanticAtlantic-4-4NN
00612787939AtlanticAtlantic-4-4NN
00616787305AtlanticAtlantic-4-4NN
96916671671UTC+10Pacific/1014NN
96917671671UTC+10Pacific/1014NN
96939671969UTC+9Japan915NY
96940680969UTC+9Japan915NY

Thanks!


Accepted Solutions
Solution
‎07-03-2013 08:55 PM
Super User
Super User
Posts: 6,500

Re: transpose

You could use a double PROC TRANSPOSE.  Code below will convert numeric to character.  You could split the transpose into numeric and character groups and then remerge the two after the second proc transpose if you need to keep numbers as numbers.

data have;

input _TYPE_ $ _OBS_ Zipcode $ Areacode $ Timezone $ GMT DST $;

cards;

BASE 4 00602 787 Atlantic -4 N

COMPARE 4 00602 939 Atlantic -4 N

BASE 11 00612 787 Atlantic -4 N

COMPARE 11 00612 939 Atlantic -4 N

BASE 14 00616 787 Atlantic -4 N

COMPARE 14 00616 305 Atlantic -4 N

BASE 39749 96916 671 UTC+10 10 N

COMPARE 39749 96916 671 Pacific/ 14 N

BASE 39750 96917 671 UTC+10 10 N

COMPARE 39750 96917 671 Pacific/ 14 N

BASE 39758 96939 671 UTC+9 9 N

COMPARE 39758 96939 969 Japan 15 Y

BASE 39759 96940 680 UTC+9 9 N

COMPARE 39759 96940 969 Japan 15 Y

;

proc transpose data=have out=tall;

  by zipcode _type_  notsorted;

  var _all_ ;

run;

proc sort data=tall;

  by zipcode _name_;

run;

proc transpose data=tall out=want(drop=_name_) delimiter=_;

  where upcase(_name_) not in ('_OBS_','_TYPE_','ZIPCODE');

  by zipcode ;

  id _type_ _name_ ;

  var col1 ;

run;


proc print;

run;

                     C                                    C

                     O                                    O

                     M                                    M

               B     P                         B          P

               A     A                         A          A

               S     R        C        C       S          R

               E     E        O        O       E          E

               _     _        M        M       _          _

               A     A    B   P   B    P       T          T

        Z      r     r    A   A   A    A       i          i

        i      e     e    S   R   S    R       m          m

        p      a     a    E   E   E    E       e          e

        c      c     c    _   _   _    _       z          z

  O     o      o     o    D   D   G    G       o          o

  b     d      d     d    S   S   M    M       n          n

  s     e      e     e    T   T   T    T       e          e

  1   00602   787   939   N   N   -4   -4   Atlantic   Atlantic

  2   00612   787   939   N   N   -4   -4   Atlantic   Atlantic

  3   00616   787   305   N   N   -4   -4   Atlantic   Atlantic

  4   96916   671   671   N   N   10   14   UTC+10     Pacific/

  5   96917   671   671   N   N   10   14   UTC+10     Pacific/

  6   96939   671   969   N   Y    9   15   UTC+9      Japan

  7   96940   680   969   N   Y    9   15   UTC+9      Japan

View solution in original post


All Replies
Super User
Posts: 17,828

Re: transpose

SAS Learning Module: How to reshape data long to wide using proc transpose

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

A data step is probably easier, in my opinion.

A datastep merge would probably also work neatly.

data want;

merge have (where=(_type_="Base") rename= (rename all vars here))

          have (where=(_type_="Compare") rename=(rename all vars here));

by common-variables;

run;

Solution
‎07-03-2013 08:55 PM
Super User
Super User
Posts: 6,500

Re: transpose

You could use a double PROC TRANSPOSE.  Code below will convert numeric to character.  You could split the transpose into numeric and character groups and then remerge the two after the second proc transpose if you need to keep numbers as numbers.

data have;

input _TYPE_ $ _OBS_ Zipcode $ Areacode $ Timezone $ GMT DST $;

cards;

BASE 4 00602 787 Atlantic -4 N

COMPARE 4 00602 939 Atlantic -4 N

BASE 11 00612 787 Atlantic -4 N

COMPARE 11 00612 939 Atlantic -4 N

BASE 14 00616 787 Atlantic -4 N

COMPARE 14 00616 305 Atlantic -4 N

BASE 39749 96916 671 UTC+10 10 N

COMPARE 39749 96916 671 Pacific/ 14 N

BASE 39750 96917 671 UTC+10 10 N

COMPARE 39750 96917 671 Pacific/ 14 N

BASE 39758 96939 671 UTC+9 9 N

COMPARE 39758 96939 969 Japan 15 Y

BASE 39759 96940 680 UTC+9 9 N

COMPARE 39759 96940 969 Japan 15 Y

;

proc transpose data=have out=tall;

  by zipcode _type_  notsorted;

  var _all_ ;

run;

proc sort data=tall;

  by zipcode _name_;

run;

proc transpose data=tall out=want(drop=_name_) delimiter=_;

  where upcase(_name_) not in ('_OBS_','_TYPE_','ZIPCODE');

  by zipcode ;

  id _type_ _name_ ;

  var col1 ;

run;


proc print;

run;

                     C                                    C

                     O                                    O

                     M                                    M

               B     P                         B          P

               A     A                         A          A

               S     R        C        C       S          R

               E     E        O        O       E          E

               _     _        M        M       _          _

               A     A    B   P   B    P       T          T

        Z      r     r    A   A   A    A       i          i

        i      e     e    S   R   S    R       m          m

        p      a     a    E   E   E    E       e          e

        c      c     c    _   _   _    _       z          z

  O     o      o     o    D   D   G    G       o          o

  b     d      d     d    S   S   M    M       n          n

  s     e      e     e    T   T   T    T       e          e

  1   00602   787   939   N   N   -4   -4   Atlantic   Atlantic

  2   00612   787   939   N   N   -4   -4   Atlantic   Atlantic

  3   00616   787   305   N   N   -4   -4   Atlantic   Atlantic

  4   96916   671   671   N   N   10   14   UTC+10     Pacific/

  5   96917   671   671   N   N   10   14   UTC+10     Pacific/

  6   96939   671   969   N   Y    9   15   UTC+9      Japan

  7   96940   680   969   N   Y    9   15   UTC+9      Japan

Frequent Contributor
Posts: 87

Re: transpose

Thank you! works great.

☑ This topic is SOLVED.

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

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