BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Giovani
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

10 REPLIES 10
Reeza
Super User
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.

ballardw
Super User

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. 

 

Giovani
Obsidian | Level 7

Thanks for your information.

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

ballardw
Super User

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.

novinosrin
Tourmaline | Level 20

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;

Giovani
Obsidian | Level 7

It is giving error on the count statement

Kurt_Bremser
Super User

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;
Giovani
Obsidian | Level 7

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? 

Kurt_Bremser
Super User

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

Giovani
Obsidian | Level 7

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 10 replies
  • 3189 views
  • 1 like
  • 5 in conversation