BookmarkSubscribeRSS Feed
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6
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.
6 REPLIES 6
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6
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,,
Ksharp
Super User
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
art297
Opal | Level 21
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
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6
Art297,

It works great.

Thank you very much!!
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6
Ksharp,

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 810 views
  • 0 likes
  • 4 in conversation