DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 96

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

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_IDTransaction_IDItem_IDDay_of_the_weekSale_dateQuantityStore_DepartmentItem_NameItem_Category
2492372342210145027 Sun08/04/20011GROCERY DEPTPC SQZ KETCHUP 28OZOTHER
2492372342210173107 Sun08/04/20011DELI DEPTROAST BEEFCOLD CUTS
2492372342210173307 Sun08/04/20011DELI DEPTHOFMANN HOT DOGCOLD CUTS
2492372342210175467 Sun08/04/20011DELI DEPTCOLE SLAWSALADS
2492372342210176377 Sun08/04/20011PRODUCE DEPTFANCY BANANAPRODUCE
2492373142410190332 Tue10/04/20012MEAT DEPTSWFT MAPLE B&S LINKSMEAT
2492373142410205402 Tue10/04/20011FROZEN DEPTRICHS BREAD DOUGH 5LVSFROZEN FOODS
2492373142410280562 Tue10/04/20011DAIRY DEPTBYRNE SKIM MILK QTDAIRY
2492373142410280572 Tue10/04/20011DAIRY DEPTBYRNE CHOC MILK QTDAIRY
2492373542510280564 Thu12/04/20011DAIRY DEPTBYRNE SKIM MILK QTDAIRY
Super User
Posts: 5,441

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

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

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

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

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

Posted in reply to Sudhakar_A

Hi Sudhakar,

Any pattern will do..

Thank You

Alice

Super User
Posts: 10,048

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

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

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

Many Thanks Ksharp...

Alice

Contributor
Posts: 60

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

data have ;

     set have;

     by id date;

     retain Transaction_ID;

     if first.date then Transaction_ID=37245500+1;

run;

Contributor
Posts: 60

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

Hi Alice,

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

Sudhakar

Ask a Question
Discussion stats
  • 7 replies
  • 317 views
  • 0 likes
  • 4 in conversation