topic Re: Count the number of occurrences per weeks in SAS Programming

Count the number of occurrences per weeks

Giovani — Mon, 11 Dec 2017 22:40:13 GMT

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;

Re: Count the number of occurrences per weeks

Reeza — Mon, 11 Dec 2017 22:50:25 GMT

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.

Re: Count the number of occurrences per weeks

ballardw — Mon, 11 Dec 2017 23:02:41 GMT

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.

Re: Count the number of occurrences per weeks

novinosrin — Mon, 11 Dec 2017 23:42:34 GMT

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;

Re: Count the number of occurrences per weeks

Kurt_Bremser — Tue, 12 Dec 2017 08:06:57 GMT

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;

Re: Count the number of occurrences per weeks

Giovani — Tue, 12 Dec 2017 21:21:15 GMT

Thanks for your information.

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

Re: Count the number of occurrences per weeks

Giovani — Tue, 12 Dec 2017 21:22:47 GMT

It is giving error on the count statement

Re: Count the number of occurrences per weeks

Giovani — Tue, 12 Dec 2017 21:33:41 GMT

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?

Re: Count the number of occurrences per weeks

ballardw — Tue, 12 Dec 2017 22:33:02 GMT

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

The generic form is

ods <destination> <options>;

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";

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.

Re: Count the number of occurrences per weeks

Kurt_Bremser — Wed, 13 Dec 2017 07:04:00 GMT

@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.

Re: Count the number of occurrences per weeks

Giovani — Wed, 13 Dec 2017 15:43:35 GMT

KurtBremseryou are fantastic!!!!!!!!!

Thank very much!!!!!