topic LAYOUT in SAS Programming

LAYOUT

Gladis6680 — Thu, 12 Sep 2019 17:18:36 GMT

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

Re: LAYOUT

tomrvincent — Thu, 12 Sep 2019 17:24:17 GMT

use PROC TABULATE instead.

Re: LAYOUT

Reeza — Thu, 12 Sep 2019 17:37:05 GMT

You can WEIGHT with the SALES to get the values. If you have negative values do not do this though. Otherwise, PROC TABULATE is a an option if you want displayed output. If you want a table instead, use PROC TRANSPOSE.

Re: LAYOUT

ballardw — Thu, 12 Sep 2019 18:01:59 GMT

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.

Re: LAYOUT

Gladis6680 — Wed, 18 Sep 2019 19:41:02 GMT

Thank you! I will work on the proc tabulate. I converted the FISCAL_PERIOD into Format=MONYY7. The type is numeric