topic Re: How to create a TransactionId field, Please Help. Many thanks in SAS Programming

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

Question — Tue, 14 May 2013 13:48:00 GMT

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

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

LinusH — Tue, 14 May 2013 14:03:22 GMT

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.

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

Sudhakar_A — Wed, 15 May 2013 08:25:49 GMT

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 ?

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

Question — Wed, 15 May 2013 10:55:40 GMT

Hi Sudhakar,

Any pattern will do..

Thank You

Alice

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

Ksharp — Wed, 15 May 2013 11:30:12 GMT

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

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

Question — Wed, 15 May 2013 11:39:32 GMT

Many Thanks Ksharp...

Alice

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

Sudhakar_A — Wed, 15 May 2013 12:05:14 GMT

Hi Alice,

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

Sudhakar

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

Sudhakar_A — Wed, 15 May 2013 12:07:24 GMT

data have ;

set have;

by id date;

retain Transaction_ID;

if first.date then Transaction_ID=37245500+1;

run;