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
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?
Hi - total items per transaction please.
Thanks
A
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)
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;
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.
I forgot the data set! Here it is.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.