Proc transpose get avg by day of week

Reply
Regular Contributor
Posts: 240

Proc transpose get avg by day of week

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

Super User
Posts: 11,105

Re: Proc transpose get avg by day of week

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.

Regular Contributor
Posts: 240

Re: Proc transpose get avg by day of week

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

Super User
Posts: 11,105

Re: Proc transpose get avg by day of week

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.

Regular Contributor
Posts: 240

Re: Proc transpose get avg by day of week

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

Super User
Super User
Posts: 7,682

Re: Proc transpose get avg by day of week

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;

Regular Contributor
Posts: 240

Re: Proc transpose get avg by day of week

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

Super User
Super User
Posts: 7,682

Re: Proc transpose get avg by day of week

Hi,

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

SQL Tutorial

Ask a Question
Discussion stats
  • 7 replies
  • 344 views
  • 0 likes
  • 3 in conversation