topic Re: Unique count of frequencies proc tabulate in SAS Programming

Unique count of frequencies proc tabulate

dcortell — Fri, 10 Mar 2023 13:16:14 GMT

Hi experts

I have the following dataset, where the same id could be present multiple time in combos

id sales zone
1   2  "a"
1  3  "b"
1  5  "c"

There is any way to have proc tabulate compute distinct frequencies of "id" ?

Re: Unique count of frequencies proc tabulate

HarrySnart — Fri, 10 Mar 2023 14:07:40 GMT

Does it need to be PROC TABULATE? You could do this very easily with count distinct in PROC SQL

Re: Unique count of frequencies proc tabulate

dcortell — Fri, 10 Mar 2023 14:59:55 GMT

Hi Harry

I simplified the tabulate code, but it does other other summarization. So yes, I need it in proc tabulate

Re: Unique count of frequencies proc tabulate

ballardw — Fri, 10 Mar 2023 16:00:15 GMT

@dcortell wrote:

Hi Harry

I simplified the tabulate code, but it does other other summarization. So yes, I need it in proc tabulate

Show your code.

I ask to see your code because if you want this value in an existing report table then the structure of the remaining code is needed to prevent things like crossing variables incorrectly.

Second, what do you actually expect for out? I am afraid that "unique count of frequencies" may have more than one interpretation. Plus when your example data only shows one value for ID there isn't much clarity about what you are using for input.

I suspect that you just need an N statistic in the right place, but do need the rest of your code.

Re: Unique count of frequencies proc tabulate

dcortell — Fri, 10 Mar 2023 17:03:23 GMT

Sample dataset

id_brand	sales	zone
1	5	a
1	6	b
2	4	a

Sample code (metacode)

proc tabulate data=data;
   class
   id_brand zone;
   var sales;
   table zone,

   sales*sum
   id_brand*N="unique ids" (need unique N of ids) /rts=50
             ;
run;

I'm trying to understand if there is a N distinct approach to use in proc tabulate

Re: Unique count of frequencies proc tabulate

ballardw — Fri, 10 Mar 2023 17:19:10 GMT

Still don't see how you define "unique count". With the table that you show do you mean that "unique" count should be within each Zone? Or overall in the data?

I think that you need to add a variable to you data set that is likely numeric that is assigned a value of one for only one record of each each id and then sum that. But where/how it appears in the table makes a difference on how that variable is made.

Note, 3 records, especially without an example of how you want the result to appear is pretty slim to work with.

Re: Unique count of frequencies proc tabulate

dcortell — Mon, 13 Mar 2023 11:34:22 GMT

Hi Ballard

Yes, it was in the end how to I managed the unique count, adding a "1" flag for each unique combination of zone. I was hoping there was some "distinct" statement available in proc tabulate for unique frequencies, but it seems that the way to go is to manipulate the dataset.

Thanks