topic How to group userid, cardid with multiple transaction codes in SAS Programming

How to group userid, cardid with multiple transaction codes

buddha_d — Fri, 01 Nov 2024 22:56:32 GMT

data have;
input USERID $4. CARDID :$10. TRANCODE $4.;
cards;
1234 1234567890 3503
1234 1234567890 2013
1234 1234567890 7113
5678 2345678901 2013
5678 2345678901 7113
8907 3456789012 3503
8907 3456789012 2013
7658 4567890123 7113
7658 4567890123 7114
4356 5678901234 3503
4356 5678901234 2013
4356 5678901234 7113
9874 6789012345 3503
9874 6789012345 7113
5437 7890123456 3503
5461 8901234567 7113
5462 9012345678 2013
2543 0123456789 2013
2543 0123456789 7113
2543 0123456789 3503
;
run;

I have huge dataset and the above data is dummy to replicate that dataset.

I have a dataset which has card userid, cardid and transaction code. I have the following conditions to filter the data.

1. I want to get the records that have all userid and cardids with the trancodes of '3503' along with '2013' and '7113'

2. I don't need to get any records associated with just ('3503' and '2013') or ('2013' and '7113') or ('3503' and '7113') or individual tran codes

3. I have to have '3503' first and along with that I need to have trancodes of '2013' and '7113'

I need to see the output result like this:

USERID CARDID TRANCODE
1234 1234567890 3503
1234 1234567890 2013
1234 1234567890 7113
4356 5678901234 3503
4356 5678901234 2013
4356 5678901234 7113
2543 0123456789 2013
2543 0123456789 7113
2543 0123456789 3503

I tried this code and wasn't successful

PROC SQL;
CREATE TABLE WANT AS 
SELECT * 
FROM HAVE
WHERE TRANCODE IN ('3503','2013','7113')
;
QUIT;

Please help me. Thanks in advance

Re: How to group userid, cardid with multiple transaction codes

Ksharp — Sat, 02 Nov 2024 06:45:51 GMT

If I understood what you mean.

data have;
input USERID $4. CARDID :$10. TRANCODE $4.;
cards;
1234 1234567890 3503
1234 1234567890 2013
1234 1234567890 7113
5678 2345678901 2013
5678 2345678901 7113
8907 3456789012 3503
8907 3456789012 2013
7658 4567890123 7113
7658 4567890123 7114
4356 5678901234 3503
4356 5678901234 2013
4356 5678901234 7113
9874 6789012345 3503
9874 6789012345 7113
5437 7890123456 3503
5461 8901234567 7113
5462 9012345678 2013
2543 0123456789 2013
2543 0123456789 7113
2543 0123456789 3503
;
run;
proc sql;
create table want as
select * from have
 group by USERID,CARDID
  having sum(TRANCODE='3503') and sum(TRANCODE='2013') and sum(TRANCODE='7113')
;
quit;

Re: How to group userid, cardid with multiple transaction codes

Kurt_Bremser — Sat, 02 Nov 2024 06:59:31 GMT

You say you want "3503" to come first for a given group; userid "2543" does not meet that requirement.

Re: How to group userid, cardid with multiple transaction codes

Kurt_Bremser — Sat, 02 Nov 2024 07:00:28 GMT

Otherwise, this is how you solve such things with a double DO in a DATA step:

data have;
input USERID $4. CARDID :$10. TRANCODE $4.;
cards;
1234 1234567890 3503
1234 1234567890 2013
1234 1234567890 7113
5678 2345678901 2013
5678 2345678901 7113
8907 3456789012 3503
8907 3456789012 2013
7658 4567890123 7113
7658 4567890123 7114
4356 5678901234 3503
4356 5678901234 2013
4356 5678901234 7113
9874 6789012345 3503
9874 6789012345 7113
5437 7890123456 3503
5461 8901234567 7113
5462 9012345678 2013
2543 0123456789 2013
2543 0123456789 7113
2543 0123456789 3503
;

proc sort data=have;
by userid cardid;
run;

data want;
do until (last.cardid);
  set have (where=(trancode in ("3503","2013","7113")));
  by userid cardid;
  if first.cardid and trancode = "3503" then _3503 = 1;
  if trancode = "2013" then _2013 = 1;
  if trancode = "7113" then _7113 = 1;
end;
do until (last.cardid);
  set have (where=(trancode in ("3503","2013","7113")));
  by userid cardid;
  if _3503 and _2013 and _7113 then output;
end;
drop _:;
run;

Re: How to group userid, cardid with multiple transaction codes

buddha_d — Sun, 03 Nov 2024 01:32:24 GMT

Thanks Ksharp