Count the number of occurrences per weeks

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Count the number of occurrences per weeks

From the data bellow I want to count the number of brown trucks sold per week.

The week should start on Monday and finish on Sunday.

How can I do this?

 

data CarsSold;
input Observation Data model$ color$;
cards;
1 1152017 truck  brown
2 1162017 truck  gray
3 1172017 car white
4 1182017 car yellow
5 1192017 truck  brown
6 11102017 truck  brown
7 11112017 car black
8 11122017 car black
9 11132017 truck  gray
10 11142017 truck  brown
11 11152017 car white
12 11162017 car white
13 11172017 truck  gray
14 11182017 truck  gray
15 11192017 car yellow
run;


Accepted Solutions
Solution
‎12-13-2017 10:42 AM
Super User
Posts: 9,890

Re: Count the number of occurrences per weeks


Giovani wrote:

It is working, but it returns one line per day having multiple values at column number_sold.

There is away I can have only a single line for each week? 


Can't be. See this:

data CarsSold;
input Observation sell_date:mmddyy8.  model$ color$;
cards;
1 01152017 truck  brown
2 01162017 truck  gray
3 01172017 car white
4 01182017 car yellow
5 01192017 truck  brown
6 11102017 truck  brown
7 11112017 car black
8 11122017 car black
9 11132017 truck  gray
10 11142017 truck  brown
11 11152017 car white
12 11162017 car white
13 11172017 truck  gray
14 11182017 truck  gray
15 11192017 car yellow
;
run;

proc sql;
create table want as
select
  year(sell_date) as year,
  week(sell_date,'v') as week,
  count(*) as number_sold
from carssold
where model = 'truck' and color = 'brown'
group by year, week
;
quit;

proc print data=want noobs;
run;

Result:

                number_
year    week      sold

2017      2        1   
2017      3        1   
2017     45        1   
2017     46        1   

As you can clearly see, one line per week. Note that dates are stored as SAS date values, not as meaningless numbers.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 23,296

Re: Count the number of occurrences per weeks

proc freq data=have;
where color='brown';

table date;
format date weeku.;

run;

The above is an idea. You'll need to adjust your dates for it to work properly since it counts weeks as from Sunday. You could either fudge your dates by decreasing them by a day or you could look into a custom format. I thought the formats could account for a different starting day of the week but don't recall how to do that.

Super User
Posts: 13,321

Re: Count the number of occurrences per weeks

[ Edited ]

First would be to read the DATE as a SAS date value. However it isn't possible to tell in a programmatic manner whether 1152017 is supposed to be 15 Jan 2017, 11 May 2017 or 5 Nov 2017. If you have two digit days

such as 11052017 then I would Guess you mean Nov 5.

 

Why dates? Because WEEK is a date related value and you want things by week. How do you want your "week" to look like in the results?

This shows a data set with SAS date values.

data CarsSold;
   informat observation best4. date anydtdte. model color $10.;
   format date mmddyy10.;
   input Observation Date model  color ;
cards;
1 11052017 truck  brown
2 11062017 truck  gray
3 11072017 car white
4 11082017 car yellow
5 11092017 truck  brown
6 11102017 truck  brown
7 11112017 car black
8 11122017 car black
9 11132017 truck  gray
10 11142017 truck  brown
11 11152017 car white
12 11162017 car white
13 11172017 truck  gray
14 11182017 truck  gray
15 11192017 car yellow
run;

This will display the year and number of the week within the year as yyWww (the lower case are numbers the W is upper case preceding the week number.

proc freq data=carssold;
   where model='truck' and color='brown';
   tables date;
   format date weeku6.;
run;

longer versions of the weeku format will show day of week so you'd need to do a different work to see results other than a simple proc freq. 

 

Contributor
Posts: 35

Re: Count the number of occurrences per weeks

Thanks for your information.

Your code is working. How can I construct a spreadsheet with this results? 

Super User
Posts: 13,321

Re: Count the number of occurrences per weeks

The general form for sending results of procedures to specific output formats is ODS.

The generic form is

ods <destination> <options>;

 

<procedures that generate output>

ods <destination> close;

 

The destinations are numerous and specific ones have different options available. I suspect you might want

 

ods excel file="c:\path\myspreadsheetname.xlsx";

<procedure>

ods excel close;

 

Where you have c:\path point to the folder you want and use the name of the spreadsheet you want.

If you don't have the SAS/Access to PC file formats you might need to use tagsets.Excelxp which generates Excel compatible XML files.

PROC Star
Posts: 1,584

Re: Count the number of occurrences per weeks

May i request your output sample basing on the input sample you provided to help me test:

 

data CarsSold; /*copied the sample datastep Input from @ballardw */

   informat observation best4. date anydtdte. model color $10.;

   format date mmddyy10.;

   input Observation Date model  color ;

cards;

1 11052017 truck  brown

2 11062017 truck  gray

3 11072017 car white

4 11082017 car yellow

5 11092017 truck  brown

6 11102017 truck  brown

7 11112017 car black

8 11122017 car black

9 11132017 truck  gray

10 11142017 truck  brown

11 11152017 car white

12 11162017 car white

13 11172017 truck  gray

14 11182017 truck  gray

15 11192017 car yellow

run;

 

 

proc sql;

create table want as

select distinct model ,color,week(date, 'v') as week_v,  count(model) as count

from CarsSold

where color='brown' and model='truck'

group by week_v,model ,color;

quit;

Contributor
Posts: 35

Re: Count the number of occurrences per weeks

Posted in reply to novinosrin

It is giving error on the count statement

Super User
Posts: 9,890

Re: Count the number of occurrences per weeks

To be on the safe side, I'd also group by year:

proc sql;
create table want as
select
  year(sell_date) as year,
  week(sell_date,'v') as week,
  count(*) as number_sold
from carssold
where model = 'truck' and color = 'brown'
group by year, week
;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 35

Re: Count the number of occurrences per weeks

Posted in reply to KurtBremser

It is working, but it returns one line per day having multiple values at column number_sold.

There is away I can have only a single line for each week? 

Solution
‎12-13-2017 10:42 AM
Super User
Posts: 9,890

Re: Count the number of occurrences per weeks


Giovani wrote:

It is working, but it returns one line per day having multiple values at column number_sold.

There is away I can have only a single line for each week? 


Can't be. See this:

data CarsSold;
input Observation sell_date:mmddyy8.  model$ color$;
cards;
1 01152017 truck  brown
2 01162017 truck  gray
3 01172017 car white
4 01182017 car yellow
5 01192017 truck  brown
6 11102017 truck  brown
7 11112017 car black
8 11122017 car black
9 11132017 truck  gray
10 11142017 truck  brown
11 11152017 car white
12 11162017 car white
13 11172017 truck  gray
14 11182017 truck  gray
15 11192017 car yellow
;
run;

proc sql;
create table want as
select
  year(sell_date) as year,
  week(sell_date,'v') as week,
  count(*) as number_sold
from carssold
where model = 'truck' and color = 'brown'
group by year, week
;
quit;

proc print data=want noobs;
run;

Result:

                number_
year    week      sold

2017      2        1   
2017      3        1   
2017     45        1   
2017     46        1   

As you can clearly see, one line per week. Note that dates are stored as SAS date values, not as meaningless numbers.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 35

Re: Count the number of occurrences per weeks

Posted in reply to KurtBremser

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 227 views
  • 1 like
  • 5 in conversation