BookmarkSubscribeRSS Feed
Calcite | Level 5

 Hi dear,


I will be grateful if someone can help me with the following query: 

I have cases matched to controls and I need to attribute a value of a case for a given variable to the corresponding matched control. as detailed in the tables below, I need to attribute the value "1" of the "Varx' of the case to the control (case=0) so that the value of the control for varx become 1 instead of missing. 


Input table;

ID   Macth_ID  case   Varx

1     1a              1        1

2     1a              0        .


Output expected: 

ID   Macth_ID  case   Varx

1     1a              1        1

2     1a              0        1


Thanks in advance for your support.

Super User

Are you implying that your are matching on the Match_ID variable? If so, do you ever have more than two records with the same Match_id? Do you only have one non-missing value for Varx per Match_id?

Calcite | Level 5

Hi Ballardw


thanks for replying.


The data are already matched using the match_ID variable that identifies matched case and controls. Each case can have more than one control. Varx (that takes value 0 or 1) is missing for all the controls in the dataset but for each matched control the value of Varx is the same as for the case (i.e., if Varx =0 for the case, it should also be 0 for the matched controls). 

Hope this clarify.


thanks again

Super User

This is a lookup or merge. 


You can use a data step merge or SQL, and the COALESCE() function is helpful.


Examples and explanations can be found here:

Calcite | Level 5

Thank you reeza for your suggestion and the link you shared. It was helpful.

Have a nice weekend!


Community Manager

Hi arzon,


I'm glad you found some useful info! If one of the replies was the exact solution to your problem, can you "Accept it as a solution"? Or if one was particularly helpful, feel free to "Like" it. This will help other community members who may run into the same issue know what worked.


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Discussion stats
  • 5 replies
  • 1 like
  • 4 in conversation