topic Re: How to make a Three-Way Frequency Table and is there easier way ins SAS EG? in New SAS User

How to make a Three-Way Frequency Table and is there easier way ins SAS EG?

Luciferene — Tue, 25 Sep 2018 21:16:53 GMT

So each line of data I have has two numerical variables and one categorical variable.

I would like to have a report where the row and column to be range of the numerical variables and in each cell will have freq of categorical variables.

For example if I have data:

35000 3 Sold
10000 5 Lease
21000 1 Lease
21000 1 Sold
9000 0 Sold
18000 1 In
36000 12 Sold
25000 4 Lease
17000 2 Sold

I want the final format to look like:

Where the first does frequency for all the status and second only for one status.

This is what I have so far:

    data cars;
    input Price Age Status$;
    cards;
 35000 3 Sold
 10000 5 Lease
 21000 1 Lease
 21000 1 Sold
 9000 0 Sold
 18000 1 In
 36000 12 Sold
 25000 4 Lease
 17000 2 Sold
    ;
run;


proc tabulate data=cars;
class Price Age Status;
tables Price,
       Age*Status*pctn<Status>={label="%"};
run;

If anything is unclear, please let me know and I'll try my best to clarify.

I won't be back on the community until tomorrow morning.

Thanks!

Re: How to make a Three-Way Frequency Table and is there easier way ins SAS EG?

Reeza — Tue, 25 Sep 2018 21:49:56 GMT

Is age your NEW/MID/OLD? Have you tried applying any formats to your variables and then re-running the PROC TABULATE to get your output? You won't get that exact output unless you explicitly customize it though, which would mean a data step and PROC REPORT.

Re: How to make a Three-Way Frequency Table and is there easier way ins SAS EG?

Luciferene — Wed, 26 Sep 2018 14:27:29 GMT

Yes it is. Thanks for the guidance, applying formats gives information I wanted for the first table.

    DATA cars;
    INPUT Price Age Status$;
    CARDS;

	35000 3 Sold
	10000 5 Lease
	21000 1 Lease
	21000 1 Sold
	9000 0 Sold
	18000 1 In
	36000 12 Sold
	25000 4 Lease
	17000 2 Sold
    ;
RUN;

PROC FORMAT;
VALUE ageformat 	0 = "New"
					1 - 3 = "Mid"
					4 - high = "Old";	

VALUE priceformat 	0 - 10000 = "Up to $10,000"
					10001 - 20000 = "$10,001 - $20,000"
					20001 - 30000 = "$20,001 - $30,000"
					30001 - high = "$30,000+";
PICTURE mypct (ROUND) low-high='009.99%';   

RUN;

PROC TABULATE DATA=cars;
CLASS Price Age Status;

FORMAT Age ageformat.;
FORMAT Price priceformat.;

TABLES Price,
       Age*Status*(pctn<Status>*FORMAT=mypct.);
RUN;

Some formatting questions:

How would I go about removing the rows that say Status and PctN?

Is it possible for columns that aren't populated to show up as 0%? For example, only SOLD status shows up for NEW, how can I get IN and LEASE to show up even though there aren't any data that fit in that category.

On the reverse, is it possible for only the SOLD column to show and others to not show even when there are values. I think that would basically get me the second table I wanted.

Thanks!

Re: How to make a Three-Way Frequency Table and is there easier way ins SAS EG?

Reeza — Wed, 26 Sep 2018 14:42:21 GMT

@Luciferene wrote:

Some formatting questions:

How would I go about removing the rows that say Status and PctN?

On those key words in the TABLE statement set them to ' '

TABLES Price,
       Age*Status*(pctn<Status>=''*FORMAT=mypct.);

On the reverse, is it possible for only the SOLD column to show and others to not show even when there are values. I think that would basically get me the second table I wanted.

Yes, but that should happen automatically - for example in your table the OLD doesn't have an IN group.

Re: How to make a Three-Way Frequency Table and is there easier way ins SAS EG?

Luciferene — Fri, 28 Sep 2018 15:28:46 GMT

Thanks! I got it to work with the help you gave me.