topic Re: Proc transpose get avg by day of week in SAS Programming

Proc transpose get avg by day of week

BETO — Tue, 24 Mar 2015 20:15:33 GMT

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

Re: Proc transpose get avg by day of week

ballardw — Tue, 24 Mar 2015 20:48:37 GMT

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.

Re: Proc transpose get avg by day of week

BETO — Tue, 24 Mar 2015 21:35:27 GMT

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

Re: Proc transpose get avg by day of week

ballardw — Tue, 24 Mar 2015 21:52:53 GMT

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.

Re: Proc transpose get avg by day of week

BETO — Wed, 25 Mar 2015 01:42:26 GMT

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

Re: Proc transpose get avg by day of week

RW9 — Wed, 25 Mar 2015 09:13:00 GMT

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;

Re: Proc transpose get avg by day of week

BETO — Wed, 25 Mar 2015 19:51:34 GMT

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

Re: Proc transpose get avg by day of week

RW9 — Thu, 26 Mar 2015 11:05:47 GMT

Hi,

There are several SQL functions, you could try AVG(), or do it manually from count()/sum().

SQL Tutorial