topic Re: How do I conduct same date of service query in SAS EG? in SAS Enterprise Guide

How do I conduct same date of service query in SAS EG?

sas81 — Tue, 23 Aug 2016 16:03:47 GMT

Hello!

I would sincerely appreciate any insight on the following question below. Basically I need a distinct count of the cust_ids to identify item codes that were ordered on the same day by the same person. I have to complete this analysis by tomorrow. I am grateful for your time and help!

Example Dataset:

Date Item_Code item_desc cust_id

08/18/2016 1245 Pizza 5555

09/17/2014 1245 Pizza 4357

08/18/2016 3344 Chicken 5555

09/17/2014 6742 Bread 4357

10/10/2015 3217 Salad 3218

I am open to any format for the code and final data output as I will complete the analysis in excel. I just need to be able to answer the following quesion: What item_codes were ordered on the same day? How many distinct members per combination?

Final analysis I am looking to answer:

Pizza (1245) and Chicken (3344) 1 person

Pizza (1245) and bread (3344) 1 person

I am using SAS EG version 4.2

Re: How do I conduct same date of service query in SAS EG?

sas81 — Tue, 23 Aug 2016 17:15:00 GMT

Please let me know if I need to clariy my question. Thanks!

Re: How do I conduct same date of service query in SAS EG?

ChrisHemedinger — Tue, 23 Aug 2016 17:58:40 GMT

There is a better solution with hash object, I'm sure. But assuming I understand you correctly...

data codes;
infile datalines dlm=',';
length date 8 item_code 8 item_desc $ 10 cust_id 8;
format date date9.;
informat date mmddyy.;
input date Item_Code item_desc cust_id;
datalines;
08/18/2016,1245,Pizza,5555
09/17/2014,1245,Pizza,4357
08/18/2016,3344,Chicken,5555
09/17/2014,6742,Bread,4357
10/10/2015,3217,Salad,3218
08/18/2016,1245,Pizza,9999
09/17/2014,1245,Pizza,4357
08/18/2016,3344,Chicken,9999
;
run;

proc sort data=codes;
 by date cust_id Item_Code;
run;

/* assemble combos per day, per customer */
data sameday_combos(keep=date combo cust_id);
 length combo $ 40;
 retain combo prev_item;
 set codes;
 by date cust_id Item_Code ;
 if prev_item ^= item_code then do;
   combo = catx(',',item_code,combo);
   prev_item=item_code;
  end;
 if last.cust_id then do;
   output; call missing (combo,prev_item) ; 
   end;
run;

/* sum the # customers per combo, per day */
proc sql;
  create table cust_totals as
    select date, combo, count(cust_id) as Customers
    from sameday_combos
    group by date, combo;
quit;

Output:

You might have wanted each item code to be in its own variable (item_code1, item_code2, etc.) per day. That's more of a transpose operation, and you may be able to adopt the above to get that...

Chris

Re: How do I conduct same date of service query in SAS EG?

sas81 — Tue, 23 Aug 2016 18:15:21 GMT

That is perfect Chris! I wish I could thank you a million times. I researched several white papers but couldn't find what I was looking for. I really appreciate your time.