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

Accepted Solutions
Tom
Super User Tom
Super User

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;

Tom_0-1677966194490.png

 

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

Please explain the logic you would like to use.

--
Paige Miller
qc1
Fluorite | Level 6 qc1
Fluorite | Level 6
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
Tom
Super User Tom
Super User

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;

Tom_0-1677966194490.png

 

SAS Innovate 2025: Register Now

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!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 486 views
  • 1 like
  • 3 in conversation