Prev: Is there a format to print time values with leading zeros?
Next: Location Intelligence demo
From: imh bychua on 30 Mar 2010 06:40
Hi

i have a table with the following columns now :

id date1 code1 date2 code2 date3 code3 date4 code4.

i would like to find out whether is there a more efficient way to
transpose the table into

id date code
-- ---- ------

in sql, it sld be similar to

select id, date1, code1 from table
union
select id ,date2, code2 from table
union
select id, date3, code3 from table

etc...
thank you
best regards
chua
From: GuyA on 30 Mar 2010 07:03
Look up the TRANSPOSE procedure. Very simple and useful.
From: Patrick on 30 Mar 2010 07:04
Hi Chua

I believe your union statement would need some adjustments for doing
what you want it to do...

Alternatively you could use a data step like below. Besides of
performance this will also be more convenient to code the more
elements you have (in comparison to a SQL UNION).

data want(drop=i);
set have;
array date {*} date1-date3;
array code {*} code1-code3;
do i=1 to dim(date);
date=date{i};
code=code{i};
output;
end;
run;

HTH
Patrick
From: Reeza on 30 Mar 2010 16:36
On Mar 30, 3:40 am, imh bychua <imh.byc...(a)gmail.com> wrote:
> Hi
>
> i have a table with the following columns now :
>
> id   date1  code1  date2  code2   date3  code3  date4  code4.
>
> i would like to find out whether is there a more efficient way to
> transpose the table into
>
> id date code
> --  ----  ------
>
> in sql, it sld be similar to
>
> select id, date1, code1 from table
> union
> select id ,date2, code2 from table
> union
> select id, date3, code3 from table
>
> etc...
> thank you
> best regards
> chua

As GuyA stated, use the Transpose function. Easiest and most
versatile, well worth knowing.
From: Lou on 1 Apr 2010 20:58

"Reeza" <fkhurshed(a)hotmail.com> wrote in message
news:969cbf5f-1aa0-417e-92f7-ec7f4163a825(a)10g2000yqq.googlegroups.com...

(snipped)

>As GuyA stated, use the Transpose function. Easiest and most
>versatile, well worth knowing.

GuyA suggested using the transpose **procedure** (aka PROC TRANSPOSE).
Procedures and functions are not the same thing. And as of v9.1.3, SAS does
not have a "transpose function".


 |  Next  |  Last
Pages: 1 2
Prev: Is there a format to print time values with leading zeros?
Next: Location Intelligence demo