topic Re: Dynamic Tabulate in SAS Programming

Dynamic Tabulate

DavidPhillips2 — Fri, 21 Jun 2019 19:44:22 GMT

I received a request to a function that feeds in 1 to 4 columns variables from a table and 1 to 4 row variables and creates a tabulate statement from them. This part is easy. Any advice on how to dynamically handle percent of for a measure being passed?

Is there an article that gives an example?

Re: Dynamic Tabulate

Reeza — Fri, 21 Jun 2019 19:55:20 GMT

Not clear what you mean by a percent or measure being passed and what you want to do with it.

Re: Dynamic Tabulate

DavidPhillips2 — Fri, 21 Jun 2019 19:57:05 GMT

If a user wants to see a column breakout by pct of total rather than a numeric count of a column. Or a user wants to see both.

Re: Dynamic Tabulate

ballardw — Fri, 21 Jun 2019 20:03:49 GMT

@DavidPhillips2 wrote:

If a user wants to see a column breakout by pct of total rather than a numeric count of a column. Or a user wants to see both.

Is "total" a total count or a sum of some VAR variable?

COLPCTN or COLPCTSUM look like the desired statistics without a more concrete example.

Re: Dynamic Tabulate

Cynthia_sas — Fri, 21 Jun 2019 20:26:06 GMT

Hi:

TABULATE has keyword statistics ROWPCTSUM, COLPCTSUM and PCTSUM, PAGEPCTSUM, etc, that you use to control the denominator of a percent. If you are prompting them for information, you'd have to ask them what percent they want to see and start with the simple 3:

Even if you are not going to show the Grand Total or the Row Total or the Column Total, I like to make sure that people understand what the keyword statistics are going to produce before I let them pick.

If they want anything other than the N, that moves you to needing a VAR statement and a SUM statistic. The percent statistic they get will or should be based on where they expect the 100% to be. This is what I give them to start, something like:

That way, they can imagine their own data and where you see the 100 in the percent column indicates which SUM was used as the denominator for the percent calculation.

Not a full answer, but a place to start.

Cynthia

Re: Dynamic Tabulate

DavidPhillips2 — Fri, 21 Jun 2019 20:29:28 GMT

This block is likely what I need.