topic Re: unique field value against multiple values in SAS Programming

unique field value against multiple values

woo — Fri, 14 Mar 2014 23:46:21 GMT

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

Re: unique field value against multiple values

Tom — Sat, 15 Mar 2014 02:07:01 GMT

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.

Re: unique field value against multiple values

PGStats — Sat, 15 Mar 2014 02:24:07 GMT

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;

Re: unique field value against multiple values

woo — Mon, 17 Mar 2014 21:26:54 GMT

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!

Re: unique field value against multiple values

Tom — Mon, 17 Mar 2014 22:58:21 GMT

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;

Re: unique field value against multiple values

Reeza — Tue, 18 Mar 2014 00:59:16 GMT

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;

Re: unique field value against multiple values

woo — Tue, 18 Mar 2014 18:22:16 GMT

Thanks Reeza, TOM, PGStats...

Reeza - it worked grt...!!!