## DATA Step, Macro, Functions and more

Frequent Contributor
Posts: 96

Hi All,

I would like to create a transaction_id based on Sale Date ...Please see an exampe below...Each customer will have a different trans_id for each date he has transacted,,,I hoe it does make sense.

 Customer_ID Transaction_ID Item_ID Day_of_the_week Sale_date Quantity Store_Department Item_Name Item_Category 2492 3723422 1014502 7 Sun 08/04/2001 1 GROCERY DEPT PC SQZ KETCHUP 28OZ OTHER 2492 3723422 1017310 7 Sun 08/04/2001 1 DELI DEPT ROAST BEEF COLD CUTS 2492 3723422 1017330 7 Sun 08/04/2001 1 DELI DEPT HOFMANN HOT DOG COLD CUTS 2492 3723422 1017546 7 Sun 08/04/2001 1 DELI DEPT COLE SLAW SALADS 2492 3723422 1017637 7 Sun 08/04/2001 1 PRODUCE DEPT FANCY BANANA PRODUCE 2492 3731424 1019033 2 Tue 10/04/2001 2 MEAT DEPT SWFT MAPLE B&S LINKS MEAT 2492 3731424 1020540 2 Tue 10/04/2001 1 FROZEN DEPT RICHS BREAD DOUGH 5LVS FROZEN FOODS 2492 3731424 1028056 2 Tue 10/04/2001 1 DAIRY DEPT BYRNE SKIM MILK QT DAIRY 2492 3731424 1028057 2 Tue 10/04/2001 1 DAIRY DEPT BYRNE CHOC MILK QT DAIRY 2492 3735425 1028056 4 Thu 12/04/2001 1 DAIRY DEPT BYRNE SKIM MILK QT DAIRY
Super User
Posts: 5,876

What do want to do with this id?

Does it have to numerical?

Otherwise, just concatenate the two columns:

catx('_',customer_id, put(sale_date,best.)

Otherwise, have your data sorted by customer and sale date.

Then just lop tru your table with a data step and increase the trans id by 1 for each new first.sale_date.

Data never sleeps
Contributor
Posts: 60

hi,

We can create the transaction id based on each uniques customer and sale date? how do you want the transaction id should be is it a continous one? or you need any pattern  like starting with 3733141... like that ?

Frequent Contributor
Posts: 96

Hi Sudhakar,

Any pattern will do..

Thank You

Alice

Super User
Posts: 10,778

```data have;
input id date : mmddyy10.;
format      date  mmddyy10.;
cards;
2492 08/04/2001
2492 08/04/2001
2492 08/04/2001
2492 08/04/2001
2492 10/04/2001
2492 10/04/2001
2492 10/04/2001
;
run;
data have ;
set have;
by id date;
retain Transaction_ID;
if first.date then Transaction_ID=int(10000000*ranuni(0));
run;

```

Ksharp

Frequent Contributor
Posts: 96

Many Thanks Ksharp...

Alice

Contributor
Posts: 60

data have ;

set have;

by id date;

retain Transaction_ID;

if first.date then Transaction_ID=37245500+1;

run;

Contributor
Posts: 60