unique field value against multiple values

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 328
Accepted Solution

unique field value against multiple values

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...!!!


Accepted Solutions
Solution
‎03-17-2014 08:59 PM
Super User
Posts: 17,824

Re: unique field value against multiple values

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;

View solution in original post


All Replies
Super User
Super User
Posts: 6,500

Re: unique field value against multiple values

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.

Respected Advisor
Posts: 4,646

Re: unique field value against multiple values

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

PG
Super Contributor
Super Contributor
Posts: 328

Re: unique field value against multiple values

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!

Super User
Super User
Posts: 6,500

Re: unique field value against multiple values

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;

Solution
‎03-17-2014 08:59 PM
Super User
Posts: 17,824

Re: unique field value against multiple values

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;

Super Contributor
Super Contributor
Posts: 328

Re: unique field value against multiple values

Thanks Reeza, TOM, PGStats...

Reeza - it worked grt...!!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 289 views
  • 6 likes
  • 4 in conversation