unique field value against multiple values

[woo]

Hi Friends - i have question and need help if someone can please...!!!

I have 8 variables and more than 1 million records in one sas dataset. I am concern about two variable "store" and "item". in this millions of records i have duplicates records for both store and item (of course not same number of duplicates records for both variables). both are numeric in type of data. I want to counts how many multiple "item" are there for unique "store" number?

How can i do this?

Thanks in advance...!!!

[Tom]

Not sure I quite get what you want.

If you want to see counts for the combinations of value for these variable you could use PROC FREQ.

proc freq data=mydata ;

tables store*item ;

run;

So if STORE #1 has 15 observations for ITEM A then the count will be 15.

[PGStats]

Or maybe you want to know how many distinct items are associated with each store? For that, you could use:

proc sql;

select store, count(distinct item) as nbItems

from myData

group by store;

quit;

PG

[woo]

Tom - i did run it but giving so big output that is taking too much time open up.

PGStats - query giving same constant value for Item

I might not have represented my question very well i guess.

i want:

if Store "123456" has 3 Item (6578, 8766, 1256) and Sore "789101" has 5 item (6655, 2233,1467, 4545, 3333) then output would be like;

Store       Item

123456    6578

               8766

               1256

789101    6655

               2233

               1467

                4545

                3333

Thanks!

[Tom]

If you just want to list the data then use PROC PRINT.  If the same combination (for example Store=123456 and Item=6578) can appear multiple times in your data then sort it using the NODUPKEY option.

proc sort data=have (keep=store item) out=want nodupkey ;

   by store item;

run;

proc print data=want ;

   by store;

   id store ;

  var item;

run;

[Reeza · Accepted solution]

1 million records really isn't a lot.

Try outputting it to a dataset and then checking the dataset.

proc freq data=mydata noprint;

tables store*item /out=check_items_per_store;

run;

[woo]

Thanks Reeza, TOM, PGStats...

Reeza - it worked grt...!!!