topic Re: Dedup with Condition in SAS Programming

Dedup with Condition

zhige50 — Tue, 18 Jul 2023 02:32:32 GMT

I have a dataset with many duplicates for each customer ID, what I want to achieve is keeping only one record in each month for one customer. If multiple records in a month, select the record with the earliest date. How could I achieve this? I have tried first. , last. but it only gives me one cust_ID one record.

for example:

cust_id date month

12345 21APR23 15:48:30

12345 23APR23 15:50:30

12345 23APR23 17:48:30

12345 25APR23 11:48:30

12345 21Jun23 15:35:30

want:

12345 21APR23 15:48:30

12345 21Jun23 15:35:30

Re: Dedup with Condition

SASKiwi — Tue, 18 Jul 2023 03:09:22 GMT

data Have;
  input cust_id $ date datetime.;
  format date datetime21. month monyy7.;
  month = intnx('MONTH', datepart(date), 0, 'B');

datalines;
12345 21APR2023:15:48:30
12345 23APR2023:15:50:30
12345 23APR2023:17:48:30
12345 25APR2023:11:48:30
12345 21Jun2023:15:35:30
;
run;

proc sort data = Have;
  by cust_id month date;
run;

data Want;
  set Have;
  by  cust_id month;
  if first.month;
run;

Re: Dedup with Condition

mkeintz — Tue, 18 Jul 2023 04:13:31 GMT

If your dataset has a DATE variable but doesn't already have a MONTH variable, you don't need to create MONTH:

data want;
  set have;
  by cust_id ;
  if first.id=1   or   intck('month',date,lag(date))^=0;
run;

Re: Dedup with Condition

ballardw — Tue, 18 Jul 2023 13:10:45 GMT

Stealing the example data set from @SASKiwi

data Have;
  input cust_id $ date datetime.;
  format date datetime21. month monyy7.;
  month = intnx('MONTH', datepart(date), 0, 'B');

datalines;
12345 21APR2023:15:48:30
12345 23APR2023:15:50:30
12345 23APR2023:17:48:30
12345 25APR2023:11:48:30
12345 21Jun2023:15:35:30
;
run;

proc summary data=have nway;
   class cust_id date;
   format date dtmonyy7. ;
   output out=want (drop=_:);
run;

proc print data=want;
   format date datetime18.;
run;

When you know an appropriate format and apply it to a Class variable, such as the Dtmonyy, then summary will return the lowest numeric value in the format created group as the value. This particular summary otherwise counts observations so we drop those count variables as not wanted for this exercise.

Note: Class may not be appropriate for large data sets with many combinations of the Class variables.

Re: Dedup with Condition

framon — Tue, 18 Jul 2023 14:21:46 GMT

Maybe give a try with a month variable and proc sort nodupkey:

data snacks;
	set SASHELP.snacks;
	month=compress(year(Date)||month(date));
run;

proc sort data=snacks nodupkey;by product month;run;