BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
helloSAS
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
Reeza
Super User

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;

Tom
Super User Tom
Super User

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

helloSAS
Obsidian | Level 7

Thank you! works great.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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