@ChrisWoo wrote:
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
This information about 3rd within a user ID has not been previously stated. This clears up my confusion. Thank you.
I will provide DATA step solutions, as I doubt seriously that SQL can do this easily, and so I will not provide SQL for this. See Maxim 14 — Use the right tool
proc sort data=transaction;
by user_id transaction_date;
run;
data want;
set transaction;
by user_id;
if first.user_id then count=0;
count+1;
if count=3 then output;
run;
Please explain further. Please tell us the logic that makes these two transactions are the 3rd of something. I would imagine that most answers will not use SQL (despite your request) as this is sooooo much easier in a DATA step.
145 ; 16.3 ; 05/02/2022 14:00:00 111 ; 84 ; 05/02/2022 14:15:00
Also, please (when you get a chance) provide the data as working SAS data step code (examples and instructions)
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.
@ChrisWoo wrote:
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
This information about 3rd within a user ID has not been previously stated. This clears up my confusion. Thank you.
I will provide DATA step solutions, as I doubt seriously that SQL can do this easily, and so I will not provide SQL for this. See Maxim 14 — Use the right tool
proc sort data=transaction;
by user_id transaction_date;
run;
data want;
set transaction;
by user_id;
if first.user_id then count=0;
count+1;
if count=3 then output;
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.