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;
@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.
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.
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.
Thanks for your information.
Your code is working. How can I construct a spreadsheet with this results?
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.
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;
It is giving error on the count statement
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;
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?
@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.
KurtBremseryou are fantastic!!!!!!!!!
Thank very much!!!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.