BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ChrisWoo
Obsidian | Level 7
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
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

 

 

--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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)

--
Paige Miller
ChrisWoo
Obsidian | Level 7
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.

PaigeMiller
Diamond | Level 26

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

 

 

--
Paige Miller

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 287 views
  • 0 likes
  • 2 in conversation