topic Re: how to use three tables to return results？ in SAS Programming

how to use three tables to return results？

qc1 — Sat, 04 Mar 2023 16:23:11 GMT

I have three tables for event: event21, event22, event23, and one member table. How can I return Event col only based on the year of member’s birthday?

I have tables like this:
Codes Event21
A CDC
F JFK
G BOB

Codes Event22
A EED
F UOP
G SNP-D
Codes Event23
A EED
F UOP
G SNP-D
K BDP

MemberID DOB Codes
123 1/30/2021 F
456 2/28/2022 G
789 3/30/2023 K

I want output to be like this:

MemberID DOB Codes Event
123 1/30/2021 F JFK
456 2/28/2022 G SNP-D
789 3/30/2023 K BDP

How should I join those tables?
Thanks!

Re: how to use three tables to return results？

PaigeMiller — Sat, 04 Mar 2023 16:34:51 GMT

Please explain the logic you would like to use.

Re: how to use three tables to return results？

qc1 — Sat, 04 Mar 2023 16:39:08 GMT

If year(dob)= 2021 then event =returns event21

Else if year(dob)=2022 then event =returns event22

Else if year(dob)= 2023 then event = returns event23

Else event=other
Something like that

Re: how to use three tables to return results？

Tom — Sat, 04 Mar 2023 21:43:21 GMT

Seems trival as long as the datasets are sorted by CODES.

data event21 ;
  input Codes $ Event21 $;
cards;
A CDC
F JFK
G BOB
;

data event22 ;
  input Codes $ Event22 $;
cards;
A EED
F UOP
G SNP-D
;

data event23 ;
  input Codes $ Event23 $;
cards;
A EED
F UOP
G SNP-D
K BDP
;

data members ;
  input MemberID DOB :mmddyy. Codes $;
  format dob yymmdd10.;
cards;
123 1/30/2021 F
456 2/28/2022 G
789 3/30/2023 K
;

data want;
  merge members(in=in1) event21-event23;
  by codes ;
  array events [2021:2023] event21-event23;
  if in1;
  if year(dob) in (2021:2023) then event = events[year(dob)];
  drop event21-event23 ;
run;