topic transpose data in SAS Procedures

transpose data

QLi — Mon, 25 Apr 2011 20:48:34 GMT

All,

I have data foramt such as:
Search_methods week_beginning count
Online Banking 03/28/11 1
Online Banking 04/11/11 9
Online Banking 04/18/11 28
Search by Name 04/04/11 1
Search by Name 04/11/11 1
Search by Name 04/18/11 4
Search by Reservation 04/11/11 8
Search by Reservation04/18/11 42

need to transpose to the following format:

Offer Search Methods 3/28/2011 4/4/2011 4/11/2011 04/18/11
Online Banking 1 0 9 28
Search by Name 0 1 1 4
Search by ReservatioN 0 0 8 42

Thanks in advance.

Re: transpose data

SPR — Mon, 25 Apr 2011 21:02:50 GMT

Hello QLi,

This is a solution:
[pre]
data i;
input Search_methods $ 1-21 week_beginning $ 23-30 count 32-33;
datalines;
Online Banking 03/28/11 1
Online Banking 04/11/11 9
Online Banking 04/18/11 28
Search by Name 04/04/11 1
Search by Name 04/11/11 1
Search by Name 04/18/11 4
Search by Reservation 04/11/11 8
Search by Reservation 04/18/11 42
run;
proc transpose data=i out=r (drop=_name_);
var Count;
id week_beginning;
by Search_methods;
run;
[/pre]
Sincerely,
SPR

Re: transpose data

QLi — Mon, 25 Apr 2011 21:18:02 GMT

Thanks for your response.

according to the code;
proc transpose data=web out=T_web (drop=_name_ _label_) ;
var count;
id week_beginning ;
by Search_methods;
run;

the result is the following.
Search_methods 03/28/11 04/11/11 04/18/11 04/04/11
Online Banking 1 9 28 0
Search by Name 0 1 4 1
Search by Reservation 0 8 42 0

The week beginning can not be ordered. I want to them in the order of 03/28/11,04/04/11, 04/11/11,04/18/11.

I want to transpose by search methods at the same time week beginnings in order.

Thanks,,

Re: transpose data

Ksharp — Tue, 26 Apr 2011 03:25:13 GMT

How about this:

[pre]
data i;
input Search_methods $ 1-21 week_beginning : mmddyy10. count 32-33;
format week_beginning mmddyy8.;
datalines;
Online Banking 03/28/11 1
Online Banking 04/11/11 9
Online Banking 04/18/11 28
Search by Name 04/04/11 1
Search by Name 04/11/11 1
Search by Name 04/18/11 4
Search by Reservation 04/11/11 8
Search by Reservation 04/18/11 42
;
run;
proc sql;
create table temp as
select *
from (select distinct search_methods from i),(select distinct week_beginning from i)
order by search_methods
;

create table op as
select temp.search_methods,temp.week_beginning,coalesce(i.count,0) as count
from temp left join i
on temp.search_methods = i.search_methods
and temp.week_beginning = i.week_beginning
;
quit;
options validvarname=any;
proc transpose data=op out=want(drop=_name_ ) ;
by search_methods;
id week_beginning;
var count;
run;
[/pre]

Ksharp

Re: transpose data

art297 — Tue, 26 Apr 2011 12:37:51 GMT

QLi,

Couldn't you obtain what you want with proc report? E.g.,

[pre]
proc report list nowd split='~';
columns Search_methods week_beginning,(count dummy);
define Search_methods / group 'Search_methods' '--';
define week_beginning/ across; /*ordered by date*/
define count / sum width=10 '--';
define dummy / noprint;
run;
[/pre] Message was edited by: art297

Re: transpose data

QLi — Tue, 26 Apr 2011 12:55:25 GMT

Art297,

It works great.

Thank you very much!!

Re: transpose data

QLi — Tue, 26 Apr 2011 12:56:08 GMT

Ksharp,

I tried your method. It worked great. Thank for your help.