topic Re: transpose in SAS Programming

transpose

helloSAS — Wed, 03 Jul 2013 22:20:50 GMT

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_	Zipcode	Areacode	Timezone	GMT	DST
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

Table 2

Zipcode	BASE Area code	COMPARE Area Code	Base TmeZone	Compare TimeZone	Base GMT	Compare GMT	Base DST	Compare DST
00602	787	939	Atlantic	Atlantic	-4	-4	N	N
00612	787	939	Atlantic	Atlantic	-4	-4	N	N
00616	787	305	Atlantic	Atlantic	-4	-4	N	N
96916	671	671	UTC+10	Pacific/	10	14	N	N
96917	671	671	UTC+10	Pacific/	10	14	N	N
96939	671	969	UTC+9	Japan	9	15	N	Y
96940	680	969	UTC+9	Japan	9	15	N	Y

Thanks!

Re: transpose

Reeza — Wed, 03 Jul 2013 22:27:58 GMT

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;

Re: transpose

Tom — Thu, 04 Jul 2013 00:55:07 GMT

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

Re: transpose

helloSAS — Fri, 05 Jul 2013 14:29:30 GMT

Thank you! works great.