How to create a TransactionId field, Please Help. Many thanks

[Question]

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.

Many Thanks for your help..

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

[LinusH]

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.

[Sudhakar_A]

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 ?

[Question]

Hi Sudhakar,

Any pattern will do..

Thank You

Alice

[Ksharp]

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

[Question]

Many Thanks Ksharp...

Alice

[Sudhakar_A]

data have ;

     set have;

     by id date;

     retain Transaction_ID;

     if first.date then Transaction_ID=37245500+1;

run;

[Sudhakar_A]

Hi Alice,

Ksharp is correct actually that is the answer i was about to give.

Sudhakar