topic Re: Find out 3rd transaction and it’s user ID in SAS Programming

Find out 3rd transaction and it’s user ID

ChrisWoo — Thu, 27 Jul 2023 09:32:52 GMT

Hi guys, any idea on how do I find the 3rd transaction using proc sql ?

The data as below
User_id ; Spend ; Transaction_date
111 ; 100.5 ; 08/01/2022 11:13:00
111 ; 55 ; 10/01/2022 12:00:00
121 ; 35 ; 18/01/2022 12:00:00
145 ; 24 ; 26/01/2022 08:00:00
145 ; 89 ; 05/02/2022 11:00:00
145 ; 16.3 ; 05/02/2022 14:00:00
111 ; 84 ; 05/02/2022 14:15:00
111 ; 30 ; 13/02/2022 12:31:00

Sry for the bad display of data, because i type it using my phone.

My desired output ;
User_id ; Spend ; Transaction_date
145 ; 16.3 ; 05/02/2022 14:00:00
111 ; 84 ; 05/02/2022 14:15:00

Re: Find out 3rd transaction and it’s user ID

PaigeMiller — Thu, 27 Jul 2023 09:42:55 GMT

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)

Re: Find out 3rd transaction and it’s user ID

ChrisWoo — Thu, 27 Jul 2023 13:56:12 GMT

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.

Re: Find out 3rd transaction and it’s user ID

PaigeMiller — Thu, 27 Jul 2023 14:03:40 GMT

@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;