BookmarkSubscribeRSS Feed
hwangnyc
Quartz | Level 8

Hi everyone,

 

I have a dataset that has 3 columns:

 

Date - Date of the event

Car - the number of the car

Client - The person who is riding in the car

 

If on a given date, a car has more than one client, they have two rows. I would like to make the data wide so I can get the average per car over a period of time. 

 

The data that I have is this:

 

DateCarClient
01/01/181Ente
01/02/181Patterson
01/02/181Jeter
01/02/182Davidson
01/03/181Steve
01/03/181Gonzalez 
01/03/182York
01/03/182Camara
01/03/182Camara
01/04/181Waters
01/04/181Ventre
01/04/182Perez
01/04/183Rodriguez
01/04/183Jeter
01/04/183Jiminez

 

The data that I would like is this:

 

DateCAR1_CountCar2_CountCar3_CountTotalCount
1/1/20181001
1/2/20182103
1/3/20182305
1/4/20182136

 

Any suggestions would be greatly appreciated.

 

Thank you!

4 REPLIES 4
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

 

data have;
infile cards missover dlm=' ';
input Date $ Car Client $;
cards;
01/01/18 1 Ente
01/02/18 1 Patterson
01/02/18 1 Jeter
01/02/18 2 Davidson
01/03/18 1 Steve
01/03/18 1 Gonzalez 
01/03/18 2 York
01/03/18 2 Camara
01/03/18 2 Camara
01/04/18 1 Waters
01/04/18 1 Ventre
01/04/18 2 Perez
01/04/18 3 Rodriguez
01/04/18 3 Jeter
01/04/18 3 Jiminez
;

proc freq data=have ;
	tables date*car / out=want;
run;
proc sql;
	create table want2 as
	select date, count(date) as TotalCount
	from have
	group by date;
quit;
proc sort data=want;
by date car;
run;
data want3;
	merge want2 want;
	by date;
run;
proc transpose data=want3(drop=percent) out=want4(drop=_name_ _label_) prefix=Count_car ;
	id car;
	by date totalcount;
run;
Astounding
PROC Star

PROC TABULATE should be able to do that easily.  Untested but definitely in the right neighborhood:

 

proc tabulate data=have;

class date car;

tables date, (car all)*n=' ';

run;

Reeza
Super User
Why do you think you need it wide to get summary statistics? Wouldn't PROC MEANS with some CLASS or formats work just as well?
ballardw
Super User

This uses Proc means to display the mean number of riders per car per day that the car was used.

data have;
infile cards missover dlm=' ';
input Date $ Car Client $;
cards;
01/01/18 1 Ente
01/02/18 1 Patterson
01/02/18 1 Jeter
01/02/18 2 Davidson
01/03/18 1 Steve
01/03/18 1 Gonzalez 
01/03/18 2 York
01/03/18 2 Camara
01/03/18 2 Camara
01/04/18 1 Waters
01/04/18 1 Ventre
01/04/18 2 Perez
01/04/18 3 Rodriguez
01/04/18 3 Jeter
01/04/18 3 Jiminez
;

proc summary data=have nway;
   class date car;
   output out=havesum (drop=_type_);
run;

proc means data=havesum (rename=(_freq_=count)) mean;
   class car;
   var count;
run;
   
 

If you actually need a wider period of time you may need to include the days with 0 riders depending on what you mean by average per car over a period of time. 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1100 views
  • 0 likes
  • 5 in conversation