topic Total per month SAS EG in New SAS User

Total per month SAS EG

AK100 — Tue, 24 Mar 2020 07:24:20 GMT

Hello lads,

I'am quite new to SAS EG so the question I will ask might be simple but I can't get it done. My current dataset looks like this (example, because in reality its much bigger):

Location - VehicleType - TotalVehicles - Date

2646 - AAA - 06 - 10December2019.

2646 - GGG - 13 - 10December2019.

2646 - AAA - 02 - 10December2019.

2646 - FFF - 11 - 10December2019.

2646 - GGG - 02 - 10December2019.

2646 - CCC- 11 - 10December2019.

I would like to have the total vehicles that drove on that location per month. So as an example, I want the following:

Location - VehicleType - TotalVehicles - Date

2646 - AAA- 20 - December2019.

2646 - TTT - 78 - December2019

2646 - GGG- 230- December2019.

2646 - BBB- 780 - December2019

2646 - HHH- 20 - December2019.

2646 - NNN- 78 - December2019

I actually want the total per month per vehicle type. That means each vehicletype must have just 1 date variable.

Can somebody help me with this? Appreciate it.

Re: Total per month SAS EG

Kurt_Bremser — Mon, 23 Mar 2020 16:11:49 GMT

In your expected result, you have three observations for 2646-AAA-December2019. That does not make sense.

@AK100 wrote:

Hello lads,

I'am quite new to SAS EG so the question I will ask might be simple but I can't get it done. My current dataset looks like this (example, because in reality its much bigger):

Location - VehicleType - TotalVehicles - Date

2646 - AAA - 06 - 10December2019.

2646 - GGG - 13 - 10December2019.

2646 - AAA - 02 - 10December2019.

2646 - FFF - 11 - 10December2019.

2646 - GGG - 02 - 10December2019.

2646 - CCC- 11 - 10December2019.

I would like to have the total vehicles that drove on that location per month. So as an example, I want the following:

Location - VehicleType - TotalVehicles - Date

2646 - AAA- 20 - December2019.

2646 - AAA - 78 - December2019

2646 - GGG- 230- December2019.

2646 - BBB- 780 - December2019

2646 - AAA- 20 - December2019.

2646 - BBB - 78 - December2019

I actually want the total per month per vehicle type. That means each vehicletype must have just 1 date variable.

Can somebody help me with this? Appreciate it.

Re: Total per month SAS EG

ballardw — Tue, 24 Mar 2020 14:16:22 GMT

Is your current date variable character or a SAS date valued numeric with a format to display values in the 10December2019 format?

If you have a SAS date value then you can use a format on the variable to create groups.

The SASHELP.STOCKS data set that you likely have available has date for the daily Stock price information like open, high , low and close.
Since the date is a SAS date value using a DATE7. format to do a monthly summary you could use a YYMM or YYMON format to show a summary for a month.

Proc means data=sashelp.stocks min mean max;
   class stock date;
   format date yymon7.;
   var open close high low;
run;

In SAS you will find that if you date to time values that you want to make sure your variable is the appropriate date, time or datetime value so you can do manipulation and display with the SAS functions, supplied formats or even create your own display formats.

The groups created by a format will generally work for analysis, reporting or graphing.

So by changing the format in the above Proc Means code from yymon7 to yyq. I could get a calendar quarterly summary, or using the YEAR format an annual summary.

Re: Total per month SAS EG

AK100 — Tue, 24 Mar 2020 07:25:01 GMT

That is indeed true, I've changed it. Thx.

Re: Total per month SAS EG

Kurt_Bremser — Tue, 24 Mar 2020 07:36:07 GMT

The simplest way is just a quick SQL query:

proc sql;
create table want as
select
  location,
  vehicletype,
  intnx('month',date,0,'b') as date format=yymmd7.,
  sum(totalvehicles) as totalvehicles
from have
group by location, vehicletype, calculated date;
quit;

Re: Total per month SAS EG

AK100 — Tue, 24 Mar 2020 07:43:54 GMT

This worked fine for me Thank you!!

Re: Total per month SAS EG

AK100 — Tue, 24 Mar 2020 07:45:05 GMT

I did not understand this method, the method of Kurt seemed a bit more easier. But thank you anyway, appreciate your help.

Re: Total per month SAS EG

Patrick — Tue, 24 Mar 2020 08:22:07 GMT

Assuming your desired result should actually look like...

...and you want to use the EG query builder and not code:

Re: Total per month SAS EG

AK100 — Tue, 24 Mar 2020 08:08:25 GMT

This looks okay, but I dont get your last part of the computed month value. Could you show that? Why do you add ,0.'b' after t.1.date?

Re: Total per month SAS EG

Kurt_Bremser — Tue, 24 Mar 2020 08:17:49 GMT

The INTNX function is used to increment date/time/datetime values. 0 means to stay in the same month (no increment), and 'b' means to align the result to the beginning. The way we used the function means that we always get the first day of the month as a result, allowing us to use the "date" variable as a month designator for grouping.

Re: Total per month SAS EG

Patrick — Tue, 24 Mar 2020 08:20:19 GMT

That's the syntax of the SAS intnx() function documented here.

This is a very powerful SAS function which lets you shift SAS Date values. What I'm doing here: Take a date and align it to the first day of the month the date is in. This way I then can summarize by month as now all dates are aligned to the first of the month.

Re: Total per month SAS EG

AK100 — Tue, 24 Mar 2020 08:35:38 GMT

@Patrick @Kurt_Bremser

Okay guy's that makes sense, its seems clear. However, I forgot to tell 1 thing. My Totalvehicle column is actually a COUNT of the VehicleType column.

My first intention was to SUM the VehicleType, but thats not possible ofcourse as those are not numbers but just letters.

Does the COUNT VehicleType I'am using give me the same result as the SUM vehicletype you are using @Patrick ?

The final step is to calculate percentages so I can say for example: Okay vehicletype X drove past that location 560 times in the month X, that is a percentage of 21% of all the types that drove there that month.

Re: Total per month SAS EG

Kurt_Bremser — Tue, 24 Mar 2020 08:37:29 GMT

If every observation in the source dataset represents ONE vehicle, then a coun(*) would do it instead of the sum.

Re: Total per month SAS EG

Patrick — Tue, 24 Mar 2020 08:45:09 GMT

Yeah! That's why it's important to take your time and provide representative sample data posted as a SAS Data step and then clearly show how the desired result should look like using the sample data. Such an approach removes a lot of ambiguity. You will have to spend a bit more time asking the initial question but then you'll get better answers faster.

Re: Total per month SAS EG

AK100 — Tue, 24 Mar 2020 08:43:34 GMT

It represents one measurement, so that means one vehicle type and vehicle can drive past a specific location more that once.

Re: Total per month SAS EG

AK100 — Tue, 24 Mar 2020 08:44:45 GMT

Okay Patrick, thank you.

Re: Total per month SAS EG

Kurt_Bremser — Tue, 24 Mar 2020 08:52:04 GMT

Then this will do it:

data have;
input Location $ VehicleType $ Date :date9.;
format date yymmddd10.;
datalines;
2646 AAA 10Dec2019
2646 GGG 10Dec2019
2646 AAA 10Dec2019
2646 FFF 10Dec2019
2646 GGG 10Dec2019
2646 CCC 10Dec2019
;

proc sql;
create table want as
select
  location,
  vehicletype,
  intnx('month',date,0,'b') as date format=yymmd7.,
  count(*) as totalvehicles
from have
group by location, vehicletype, calculated date;
quit;

Providing the data in a data step with datalines enables everybody to immediately rerun the code for proof or testing.