PROC SQL; CREATE TABLE TRANSACTION (USER_ID INT, SPEND NUM, TRANSACTION_DATE NUM FORMAT=DATETIME19.); INSERT INTO TRANSACTION VALUES (111 , 100.5 ,"01Feb2022 11:13:00"DT); INSERT INTO TRANSACTION VALUES (111 , 55 ,"03Feb2022 12:00:00"DT); INSERT INTO TRANSACTION VALUES (121 , 35 ,"06Feb2022 12:00:00"DT); INSERT INTO TRANSACTION VALUES (145 , 24 ,"06Feb2022 08:00:00"DT); INSERT INTO TRANSACTION VALUES (145 , 89 ,"07Feb2022 11:00:00"DT); INSERT INTO TRANSACTION VALUES (145 , 16.3 ,"08Feb2022 14:00:00"DT); INSERT INTO TRANSACTION VALUES (111 , 84 ,"08Feb2022 14:15:00"DT); INSERT INTO TRANSACTION VALUES (111 , 30 ,"13Feb2022 12:31:00"DT);
Based on the above sas code, you will get the following table:
USER_ID
SPEND
TRANSACTION_DATE
111
100.5
01FEB2022:11:13:00
111
55.0
03FEB2022:12:00:00
145
24.0
06FEB2022:08:00:00
121
35.0
06FEB2022:12:00:00
145
89.0
07FEB2022:11:00:00
145
16.3
08FEB2022:14:00:00
111
84.0
08FEB2022:14:15:00
111
30.0
13FEB2022:12:31:00
I have modified the values to shows all transaction within the month of February 2023.
and let me rephrase it as "find the 3rd transaction of the month".
Example:
User_id 111 made his/her 3rd transaction on 08FEB2022:14:15:00
User_id 145 made his/her 3rd transaction on 08FEB2022:14:00:00
Btw, please share with me on how to solve it by using both SQL and DATA step.
Beside, I'm always using SQL because it's commonly used by many companies and I'm learning it to land my first job as data analyst.
Your help is greatly appreciated.
... View more
