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 |
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.
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 ?
Hi Sudhakar,
Any pattern will do..
Thank You
Alice
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
Many Thanks Ksharp...
Alice
data have ;
set have;
by id date;
retain Transaction_ID;
if first.date then Transaction_ID=37245500+1;
run;
Hi Alice,
Ksharp is correct actually that is the answer i was about to give.
Sudhakar
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.