topic Re: Create a dataset with records which has multiple transaction in SAS Programming

Create a dataset with records which has multiple transaction

Vibcom — Wed, 15 Aug 2018 15:33:39 GMT

I have a dataset with 3 columns . For ex--

Id CustomerName TransactionDate

1 Mayank 10 aug 2018

2 Mayank 11 aug 2018

3 Akhil 10 aug 2018

4 Neha 20 aug 2018

5 Abhi 23 aug 2018

6 Neha 30 may 2017

I want to create dataset with datasteps from this dataset where customer has done multiple transaction like (Mayank , Neha) but not (AKhil , Abhi).

So Result should be--

Id CustomerName TransactionDate

1 Mayank 10 aug 2018

2 Mayank 11 aug 2018

4 Neha 20 aug 2018

6 Neha 30 may 2017

Re: Create a dataset with records which has multiple transaction

ballardw — Wed, 15 Aug 2018 15:57:30 GMT

Is there a rule for excluding the record or just a few specific ones you don't want.

If just a few specific are to be removed or ignored

data want;
   set have 
   if customername not in ('Akhil' 'Abhi');
run;

Re: Create a dataset with records which has multiple transaction

Astounding — Wed, 15 Aug 2018 16:18:08 GMT

Use PROC SORT to re-order the observations:

proc sort data=have;

by CustomerName;

run;

Then you can select those with more than one observation:

data want;

set have;

by CustomerName;

if first.CustomerName=1 and last.CustomerName=1 then delete;

run;

There are ways to do this in one step with PROC SQL, but I'm more familiar with a DATA step.

Re: Create a dataset with records which has multiple transaction

Vibcom — Wed, 15 Aug 2018 17:30:30 GMT

it is not specific for some names..

Re: Create a dataset with records which has multiple transaction

Vibcom — Wed, 15 Aug 2018 17:32:18 GMT

Thanks. It solved the problem.

Re: Create a dataset with records which has multiple transaction

shenghuani — Wed, 15 Aug 2018 17:56:09 GMT

PROC SQL;

create table data_set_new as

SELECT id, CustomerName,TransactionDate , COUNT(1) AS Cnt
FROM data_set
GROUP BY CustomerName
HAVING Cnt > 1
ORDER BY Cnt DESC;

QUIT;

Re: Create a dataset with records which has multiple transaction

Vibcom — Fri, 17 Aug 2018 13:52:45 GMT

Thanks ...your solution also can work.