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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 455 views
  • 1 like
  • 3 in conversation