topic Re: Matching clients with SAS in SAS Programming

Matching clients with SAS

macedonataan — Tue, 15 Mar 2022 06:18:25 GMT

Hi guys, let's suppose the following scenario.

Clientid Purchase_Date item_name amount

1 01/01/2020 book $20

1 01/01/2020 book $-20

2 03/01/2020 pen $2

3 04/01/2020 game $70

2 03/01/2020 pen $-2

1 06/01/2020 bike $200

Is possible to see that there is some negative values under the [amount] column, these refers to items returned to the store, and it's recorded as a negative purchase.

I would like to know how could I remove transactions (both positive and negative) for items that returned to the store?

Notes: The orders returned have the same [Clientid], [Purchase_Date], [item_name], and [amount]

Re: Matching clients with SAS

japelin — Tue, 15 Mar 2022 03:48:33 GMT

how about this

data have;
input Clientid Purchase_Date:mmddyy10. item_name $ amount:dollar4.;
format Purchase_Date mmddyy10.
       amount dollar4.;
datalines;
1 01/01/2020 book $20
1 01/01/2020 book $-20
2 03/01/2020 pen  $2
3 04/01/2020 game $70
2 03/01/2020 pen  $-2
1 06/01/2020 bike $200
;
run;

proc sql;
  create table want as
  select a.* from have as a
  left join have as b
  on a.Clientid=b.Clientid and 
     a.Purchase_Date=b.Purchase_Date and 
     a.item_name=b.item_name and
     a.amount=b.amount*(-1)
  where b.amount=.;
quit;

Re: Matching clients with SAS

KachiM — Tue, 15 Mar 2022 05:10:20 GMT

A Data Step approach. Sort the data set. Remove the pair of observations that starts with negative AMOUNT.

data have;
input Clientid Purchase_Date:mmddyy10. item_name $ amount:dollar4.;
format Purchase_Date mmddyy10.
       amount dollar4.;
datalines;
1 01/01/2020 book $20
1 01/01/2020 book $-20
2 03/01/2020 pen  $2
3 04/01/2020 game $70
2 03/01/2020 pen  $-2
1 06/01/2020 bike $200
;
run;

proc sort data = have out = have_sorted;
	by Clientid Purchase_Date item_name amount;
run;

data want;
	do until(last.Clientid);
		set have_sorted;
		by Clientid;
		if amount < 0 then do; prev = 1; delete; end;
		else if amount >= 0 and prev = 1 then do; delete; prev = .; end;
	end;
drop prev;
run;

Re: Matching clients with SAS

mkeintz — Tue, 15 Mar 2022 05:21:15 GMT

Put all the returns (amount<0) in a hash object. Read in all the purchases (amount>0), keeping only those for which there is no offsetting return. This can be done without data sorting.

data have;
input Clientid Purchase_Date:mmddyy10. item_name $ amount:dollar4.;
format Purchase_Date mmddyy10.
       amount dollar4.;
datalines;
1 01/01/2020 book $20
1 01/01/2020 book $-20
2 03/01/2020 pen  $2
3 04/01/2020 game $70
2 03/01/2020 pen  $-2
1 06/01/2020 bike $200
;
run;
data want;
  set have (where=(amount>0));
  if _n_=1 then do;
    declare hash returns (dataset:'have (where=(amount<0))');
      returns.definekey(all:'Y');
      returns.definedata(all:'Y');
      returns.definedone();
  end;
  if returns.check(key:clientid,key:purchase_date,key:item_name,key:-1*amount)^=0;
run;

This program assumes that no item is purchased more than once by a given client on a given date.

Re: Matching clients with SAS

macedonataan — Tue, 15 Mar 2022 06:44:54 GMT

Is there a way of doing it assuming the possibility of items being purchased more then once by the same client?

Re: Matching clients with SAS

mkeintz — Tue, 15 Mar 2022 16:13:18 GMT

@macedonataan wrote:

Is there a way of doing it assuming the possibility of items being purchased more then once by the same client?

"more than once by the same client", is ok but "more than once ON THE SAME DAY" would require revisions

Re: Matching clients with SAS

macedonataan — Tue, 15 Mar 2022 20:16:32 GMT

For some reason it is keeping the observation for the pen

Re: Matching clients with SAS

macedonataan — Tue, 15 Mar 2022 20:37:33 GMT

It works perfectly for the didactic example, however when applied to real data (+10Mi rows) it seems to match more observations than it should....