BookmarkSubscribeRSS Feed
hwangnyc
Quartz | Level 8

Hi everyone,

I have a dataset where I have the following variables

Date – the date of the event
Car – a specific car
Driver – name of driver
Client – name of client
Time – time of drive

Here is a sample of the data:

date

 car

driver

client

time

1/1/2018

1

AE/ JG_CF/ RZ

Ventre

0:50:00

1/2/2018

1

TB/ AE_AE/ JG

Pettaway

1:00:00

1/2/2018

1

TB/ AE_AE/ JG

Jeter

0:42:00

1/2/2018

2

CC/ RZ_JV

Davidson

0:55:00

1/3/2018

1

CC/ RZ_FS

molina de valle

0:15:00

1/3/2018

1

CC/ RZ_FS

Gonzalez

0:43:00

1/3/2018

2

TB/ AE_JV

York

0:25:00

1/3/2018

2

TB/ AE_JV

Camara

0:15:00

1/3/2018

2

TB/ AE_JV

Camara

0:20:00

1/4/2018

1

TB/ LP_JG/ MP

Waters

0:30:00

1/4/2018

1

TB/ LP_JG/ MP

Ventre

0:25:00

1/4/2018

2

CC/ RZ_VO/ FS

Perez

2:23:00

1/5/2018

1

MG/ LP_AE/ JV

Gavila

0:40:00

1/5/2018

1

MG/ LP_AE/ JV

Velasquez

0:25:00

1/5/2018

1

MG/ LP_AE/ JV

Grande

1:10:00

1/5/2018

2

CC/ RZ_JG/ SV

Dublin

0:40:00

1/5/2018

2

CC/ RZ_JG/ SV

Leobold

1:50:00

1/8/2018

1

CC/RZ_FS/JV

Davidson

0:25:00

1/8/2018

1

CC/RZ_FS/JV

Glover

0:13:00

1/8/2018

2

AE/TB_SV

Givens

0:55:00

1/9/2018

1

AE/TB_AE/JG

Jones

1:18:00

1/9/2018

1

AE/TB_AE/JG

Grande

0:20:00

1/9/2018

1

AE/TB_AE/JG

Grande

0:22:00

1/9/2018

2

CC/RZ_FS/JV

Molina de Valle

0:28:00

1/10/2018

1

CF/MG_AE

Ventre

0:15:00

1/10/2018

2

CC/RZ_JV/SV

Gonzalez

0:40:00

1/10/2018

2

CC/RZ_JV/SV

Singh

0:35:00

1/10/2018

3

AE/TB_JG/AE

Rodriguez

0:20:00

1/10/2018

3

AE/TB_JG

Jeter

0:27:00

1/10/2018

3

AE/TB_JG

Jiminez

0:35:00

 

I would like to know the following:

What is the average time if a car has 1 trip in a day, 2 trips in a day, 3 trips in a day, etc?

Any suggestions as to how to proceed would be greatly appreciated.

 

Thank you!

 

 

 

4 REPLIES 4
PaigeMiller
Diamond | Level 26

UNTESTED CODE

 

proc summary data=have;
    class date car;
    var time;
    output out=have2 mean=;
run;

proc summary data=have2(rename=(_freq_=freq));
    class freq;
    var time;
    output out=want mean=;
run;
--
Paige Miller
hwangnyc
Quartz | Level 8

Thanks Paige!

 

That gave me this output:

 

freq_TYPE__FREQTime
.07000:41:01
111040:56:18
212630:42:15
311220:32:58
41750:36:01
51350:40:46
61350:35:57
71410:37:52
81140:31:02
9150:27:59
10120:28:12
206110:40:46
414110:41:48
451110:33:40
107211

0:38:09

 

I'm not quite sure what to make of this? 

PaigeMiller
Diamond | Level 26

First, you can ignore the row where _type_=0

 

When freq=1, these are the situations where a car was driven only once in a day, and the value under TIME is the mean of all those times for cars driven once per day..

 

When freq=2, these are the situations where a car was driven twice in a day, and the value under TIME is the mean of the times when it was driven twice in a day

 

And so on

 

Please run this minor modification to the code:

 

proc summary data=have nway;
    class date car;
    var time;
    output out=have2 mean=;
run;

proc summary data=have2(rename=(_freq_=freq)) nway;
    class freq;
    var time;
    output out=want mean=;
run;

 

--
Paige Miller
ballardw
Super User

@hwangnyc wrote:

Hi everyone,

I have a dataset where I have the following variables

Date – the date of the event
Car – a specific car
Driver – name of driver
Client – name of client
Time – time of drive

Here is a sample of the data:

date

 car

driver

client

time

1/1/2018

1

AE/ JG_CF/ RZ

Ventre

0:50:00

1/2/2018

1

TB/ AE_AE/ JG

Pettaway

1:00:00

1/2/2018

1

TB/ AE_AE/ JG

Jeter

0:42:00

1/2/2018

2

CC/ RZ_JV

Davidson

0:55:00

1/3/2018

1

CC/ RZ_FS

molina de valle

0:15:00

1/3/2018

1

CC/ RZ_FS

Gonzalez

0:43:00

1/3/2018

2

TB/ AE_JV

York

0:25:00

1/3/2018

2

TB/ AE_JV

Camara

0:15:00

1/3/2018

2

TB/ AE_JV

Camara

0:20:00

1/4/2018

1

TB/ LP_JG/ MP

Waters

0:30:00

1/4/2018

1

TB/ LP_JG/ MP

Ventre

0:25:00

1/4/2018

2

CC/ RZ_VO/ FS

Perez

2:23:00

1/5/2018

1

MG/ LP_AE/ JV

Gavila

0:40:00

1/5/2018

1

MG/ LP_AE/ JV

Velasquez

0:25:00

1/5/2018

1

MG/ LP_AE/ JV

Grande

1:10:00

1/5/2018

2

CC/ RZ_JG/ SV

Dublin

0:40:00

1/5/2018

2

CC/ RZ_JG/ SV

Leobold

1:50:00

1/8/2018

1

CC/RZ_FS/JV

Davidson

0:25:00

1/8/2018

1

CC/RZ_FS/JV

Glover

0:13:00

1/8/2018

2

AE/TB_SV

Givens

0:55:00

1/9/2018

1

AE/TB_AE/JG

Jones

1:18:00

1/9/2018

1

AE/TB_AE/JG

Grande

0:20:00

1/9/2018

1

AE/TB_AE/JG

Grande

0:22:00

1/9/2018

2

CC/RZ_FS/JV

Molina de Valle

0:28:00

1/10/2018

1

CF/MG_AE

Ventre

0:15:00

1/10/2018

2

CC/RZ_JV/SV

Gonzalez

0:40:00

1/10/2018

2

CC/RZ_JV/SV

Singh

0:35:00

1/10/2018

3

AE/TB_JG/AE

Rodriguez

0:20:00

1/10/2018

3

AE/TB_JG

Jeter

0:27:00

1/10/2018

3

AE/TB_JG

Jiminez

0:35:00

 

I would like to know the following:

What is the average time if a car has 1 trip in a day, 2 trips in a day, 3 trips in a day, etc?

Any suggestions as to how to proceed would be greatly appreciated.

 

Thank you!

 

 

 


Did you have starting data where it was one time per driver per day before making the (very difficult to use consistently driver mashup variable)?

If so

proc means data=have mean;

    class car date;

    var time;

run;

 

Note that no where in your example data is actually a "number of trips per day", which you did have shown a couple of ways to get in

https://communities.sas.com/t5/New-SAS-User/Reshaping-data-from-long-to-wide-using-counts/m-p/500552

but you have chosen possibly the  most awkward way of approaching this problem.

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!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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