BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6

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

7 REPLIES 7
ballardw
Super User

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.

BETO
Fluorite | Level 6

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

ballardw
Super User

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.

BETO
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

BETO
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

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

SQL Tutorial

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
  • 7 replies
  • 926 views
  • 0 likes
  • 3 in conversation