Hi SAS Community,
I had a question regarding the layout of a table.
I have a data set, this is obtained after doing a proc summary on BEVERAGES
proc summary data=HAVE nway;
class Beverages;
var SALES;
by FISCAL_PERIOD;
output out=TABLE sum=;
run;
This is what it looks like
FISCAL_PRIOD | BEVERAGE | SALES |
JAN | Coffee | $20 |
JAN | Tea | $50 |
JAN | Milk | $70 |
JAN | Latte | $100 |
JAN | Juice | $50 |
JAN | Hot Chocolate | $30 |
JAN | Milk | $60 |
JAN | Mocha | $35 |
FEB | Milk | $30 |
FEB | Latte | $50 |
FEB | Juice | $60 |
FEB | Hot Chocolate | $55 |
MAR | Coffee | $33 |
MAR | Tea | $55 |
MAR | Milk | $85 |
MAR | Latte | $200 |
APRIL | Milk | $70 |
APRIL | Latte | $100 |
APRIL | Juice | $50 |
APRIL | Hot Chocolate | $30 |
MAY | Cappuccino | $25 |
MAY | Double expresso | $33 |
MAY | latte macchiato | $45 |
MAY | Juice | $60 |
MAY | Hot Chocolate | $55 |
JUNE | Latte | $100 |
JUNE | Juice | $50 |
JUNE | Hot Chocolate | $30 |
JUNE | Milk | $60 |
JUNE | Mocha | $35 |
But I would like it to look like the table below
BEVERAGES | JAN | FEB | MARCH | APRIL | MAY | JUNE |
Cappuccino | $25 | |||||
Double expresso | $33 | |||||
Coffee | $20 | $33 | ||||
Tea | $50 | $55 | ||||
Milk | $70 | $30 | $85 | $70 | ||
Latte | $100 | $50 | $200 | $100 | $100 | |
latte macchiato | $45 | |||||
Juice | $50 | $60 | $50 | $60 | $50 | |
Hot Chocolate | $30 | $55 | $30 | $55 | $30 | |
Milk | $60 | $60 | ||||
Mocha | $35 | $35 |
I have tried
PROC FREQ data=TABLE;
tables BEVERAGES*FISCAL_PERIOD;
run;
The above just gives me the count and not the sum.
I would appreciate your help.
Thanks
Note that getting character values like Jan Feb and such to sort properly is going to be a problem. So are your Fiscal_Period values character?
If they are character, I want to see how you got your shown order for the output of Proc Summary.
If the purpose is a report then something like:
proc tabulate data= have; class beverages fiscal_period; var sales; table beverages=' ', fiscal_period=' '*sales=' '*sum=' ' /box=beverages misstext=' ' ; run;
can get you started but there may be some order issues.
Thank you! I will work on the proc tabulate. I converted the FISCAL_PERIOD into Format=MONYY7. The type is numeric
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.