Hi have proc table looks like this
proc sql;
create table table1 as
(select
name,
day of week,
amt,
bag
from table
);
run;
data looks
nname. Day of week amt. Bag
A. Mon. 34. S
A. Mon. 23. S
A. Mon. 37. S
B. Tue. 56. S
b. Mon. 12. S
B. Thur. 56. S
what I need for the output to look is like this
Mon. Tue. WeD. Thur. Fri. Sat. Sun
A. 3. 3.
b. 1. 1. 1
since this is a monthly pull I need the avg amt of bags that come in the day of week Mon tue wed Thur fri sat sun
thank you for your help
Do you need a data set or a report?
One report :
Proc tabulate data=table1;
class DAY_Of_week;
Class name;
var amt;
table nname,
day_of_week=''* amt=''*mean=''*f=best4.
/misstext='0';
run;
I am using day_of_week as "day of week" is not a valid sas variable name.
a data set would be to summarize before transposing:
proc summary data=table nway;
class name day_of_week;
var amt;
output out=want (drop= _type_) mean=;
run;
which will generate a data set with
name day_of_week and mean of amt but called amt.
for each combination of name and day_of_week.
Then transpose with day_of_week as id and Name as by variable.
thank for your response
AMt is not that important I need to get the number of entries and group by day of week for exmple it happens 10 times every monday but once I get a count by day of week I would then want an avg of what gets produce in a month view broken out by day of week
fo exmple
feb report
name. Mon tue. Wed Thur fri sat sun
a. 50. 23. 78. 34. 12 45. 58
b. 12. 34. 76. 12. 43. 12. 12
that is avg number A has incidents coming in so our highest would be wed for a on a monthly view
You may need to clarify what your variables mean. The example makes it look like BAG is S, which is real hard to take an average of. And you didn't show any MONTH variable.
Counts per day of the week can be accomplished with N instead of MEAN in the proc tabulate or proc summary code.
I can't quite determine what you are averages as you say "avg of what gets produce in a month" but you don't say what variable contains production.
SSorry ballardw
For not explaining myself my data looks like this The data will be a month worth of data I would need to get a count of how many came in per week of day Monday tueday wed Thur fri sat sun After I get total for each day I would need than avg of the total
nname. Day of week amt. Bag
A. Mon. 34. S
A. Mon. 23. S
A. Mon. 37. S
B. Tue. 56. S
b. Mon. 12. S
B. Thur. 56. S
Hi,
proc sql;
create table TEMP as
select NNAME,
DAY_OF_WEEK,
SUM(AMT) as AMT
from HAVE
grroup by NNAME,
DAY_OF_WEEK;
quit;
proc transpose data=temp out=want;
by nname;
var amt;
id day_of_week;
idlabel day_of_week;
run;
Thanks Rw9
ITs worked great I have a question instead of sum if I wanted to get the mean of how many amts we get per day of week do change sum to count and get mean some where else ? Thanks for assistance
Hi,
There are several SQL functions, you could try AVG(), or do it manually from count()/sum().
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.