topic Need help with what PROC to use group Items per Tranaction in New SAS User

Need help with what PROC to use group Items per Tranaction

AMMAN — Mon, 15 Oct 2018 18:42:40 GMT

First time poster and a bright green SAS programmer. I have a data set I'm using for a class assignment. Variables are Date, Time, Transaction ID and Item. I need to identify how many Items were purchased in a single Transaction. As you can see, some Transactions only contain one Item, while others have multiple. I need the following variables: Date, Time, Items Per Transaction.

Any help would be appreciated! I am using EG 7.1

Re: Need help with what PROC to use group Items per Tranaction

ballardw — Mon, 15 Oct 2018 19:06:37 GMT

Items or Unique Items? In your example transaction 2 it appears that 2 of the same item may have been purchased. So would you want a count of 1 or 2 for that transaction?

Re: Need help with what PROC to use group Items per Tranaction

AMMAN — Mon, 15 Oct 2018 19:15:30 GMT

Hi - total items per transaction please.

Thanks

Re: Need help with what PROC to use group Items per Tranaction

novinosrin — Mon, 15 Oct 2018 19:28:43 GMT

Unable to test since you didn't provide sample in as plain text

Perhaps?

proc sql;

create table want as

select trans,count(distinct items) as tot

from have

group by trans

quit;

or just

count(items)

Re: Need help with what PROC to use group Items per Tranaction

V_27 — Mon, 15 Oct 2018 20:18:54 GMT

Hi @AMMAN

I am not understanding what exactly you are looking for but here is an attempt.

data want;
set have(drop=Items);
by Transaction;
if first.Transaction then count=0;
count+1;
if last.Transaction then output;
run;

Re: Need help with what PROC to use group Items per Tranaction

Astounding — Mon, 15 Oct 2018 20:13:37 GMT

Here's a simple way, but its relevance depends on what the results should be (see below).

proc freq data=have;

tables transaction * date * time / noprint out=want (keep=transaction date time count);

run;

It gives you a data set WANT that contains a new variable COUNT. Note that COUNT will be 2 (not 1) for transaction #2. There were two items in the data, but they are the same. While this solution can be adapted, as it stands it counts both of the identical items.

Re: Need help with what PROC to use group Items per Tranaction

AMMAN — Mon, 15 Oct 2018 20:42:48 GMT

I forgot the data set! Here it is.