Creating and exporting tables

[sarymay]

I am having a really hard time trying to figure out how to create a table with the columns equal to all values for one variable, the rows equal to all possible values for another variable, and the entries equal to the sum of all values for which the two values intercept. Can anyone help me with this?

EX:

color     letter     cost

blue         A          2

red          F          4

orange     C          1    

yellow     K          3

orange     C          9

yellow     K          1

blue        F          7

yellow     A          2

Result:

	A	F	C	K
Blue	2	7
Red		4
Orange			10
Yellow	2			4

[ballardw]

What do you want if there are two values of Blue A in the data? Should the Cost be added, averaged or something else?

Assuming your data is in a SAS data set and varaible names are color, letter and cost this might work if you want the sum of costs:

proc tabulate data=yourdatasetname ;

     Class color letter;

     var cost;

     table color='',

             letter=''*sum=''*cost=''*f=f6.0

             / misstext=' ';

run;

The ='' parts above, which are two single quotes but hard to read here, suppress the variable name and statistic label from appearing the output. Warning for PROC TABULATE new users, if color or letter are missing the record will be excluded from the output. The *f=f6.0 is do display the sum of cost as an integer up to 6 digits wide. The misstext option says to display a blank for missing sums of cost.

If you want a dataset then there are things to consider about what you are doing with cost.

[Reeza]

Assuming you want cost summed as in the example...

There's many ways to get the second step, but proc tabulate also shows it in that format.

I think proc summary could do it in one step, but not sure.

data have;

input colour   $  letter  $   cost;

cards;

blue      A          2

red       F          4

orange    C          1   

yellow    K          3

orange    C          9

yellow    K          1

blue      F          7

yellow    A          2

;

ods table table=summary;

proc tabulate data=have;

    class colour letter;

    var cost;

    table colour, letter*cost*sum;

run;

proc transpose data=summary out=summary2(drop=_name_);

    by colour;

    id letter;

    var cost_sum;

run;