DATA Step, Macro, Functions and more

re-coding multiple records to match the first instance of that record

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

re-coding multiple records to match the first instance of that record

[ Edited ]

Hi all, 

 

First off, I can't say enough about how great this community has been. I have been sort of thrown into learning SAS and you all have made it considerably easier to get stuff done, and I've learned a lot. Thank you. 

 

So, that being said, here's my question. 

 

I have the following dataset:

 

data have;
input patient_id Seq_num post_code $;
cards;
1 2 M4R
1 3 M4R
1 1 M4R
2 1 M4T
2 3 M4X
2 2 M4T
3 1 M4B
3 2 M4M
4 1 M4F
;
run;

 

I need to re-code the post-code variable so that all postal codes for a patient ID match whatever postal code is associated with seq_num=1

 

So, in this case, my correctly coded dataset would look like: 

 

Patient ID     Seq_Num                Post Code
1                    2                   M4R
1                    3                   M4R
1                    1                   M4R
2                    1                   M4T
2                    3                   M4T
2                    2                   M4T
3                    1                   M4B
3                    2                   M4B
4                    1                   M4F

I'm assuming there will be a data step with some sorting and some If/then statements involved, but any insights would be much appreciated. 

 

Also if anyone knows any good reference materials to get a better understanding of data steps that would be great as well, I'd love to be able to get a better handle on this and maybe even help some people in return for all the help you have given me. 

 

Thanks again. 

 

Mike 


Accepted Solutions
Solution
‎08-24-2017 08:39 AM
Occasional Contributor
Posts: 15

Re: re-coding multiple records to match the first instance of that record

Posted in reply to righcoastmike

Hi All

 

So I managed to figure it out and thought I would post the answer just in case anyone has the same issue. 

 

Here's the code: 

 

input

data have;
input patient_id Seq_num post_code $;
cards;
1 4 M4F 1 2 M4R 1 3 M4R 1 1 AAA 2 1 BBB 2 3 M4X 2 2 M4T 3 1 CCC 3 2 M4M 4 1 DDD ; run; proc sort; by patient_id seq_no; run; data work.sorted; set work.have; by patient_id; retain pcfixed; if first.patient_id then pcfixed=post_code; run;

Which produces an output of:

Patient_ID Seq_no Post_code pcfixed
       1      4      M4F       AAA
       1      2      M4R       AAA
       1      3      M4R       AAA
       1      1      AAA       AAA
       2      1      BBB       BBB
       2      3      M4X       BBB
       2      2      M4T       BBB
       3      1      CCC       CCC
       3      2      M4M       CCC
       4      1      DDD       DDD

hopefully this helps someone trying to do a similar thing in the future! 

 

Mike 

View solution in original post


All Replies
Solution
‎08-24-2017 08:39 AM
Occasional Contributor
Posts: 15

Re: re-coding multiple records to match the first instance of that record

Posted in reply to righcoastmike

Hi All

 

So I managed to figure it out and thought I would post the answer just in case anyone has the same issue. 

 

Here's the code: 

 

input

data have;
input patient_id Seq_num post_code $;
cards;
1 4 M4F 1 2 M4R 1 3 M4R 1 1 AAA 2 1 BBB 2 3 M4X 2 2 M4T 3 1 CCC 3 2 M4M 4 1 DDD ; run; proc sort; by patient_id seq_no; run; data work.sorted; set work.have; by patient_id; retain pcfixed; if first.patient_id then pcfixed=post_code; run;

Which produces an output of:

Patient_ID Seq_no Post_code pcfixed
       1      4      M4F       AAA
       1      2      M4R       AAA
       1      3      M4R       AAA
       1      1      AAA       AAA
       2      1      BBB       BBB
       2      3      M4X       BBB
       2      2      M4T       BBB
       3      1      CCC       CCC
       3      2      M4M       CCC
       4      1      DDD       DDD

hopefully this helps someone trying to do a similar thing in the future! 

 

Mike 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 38 views
  • 0 likes
  • 1 in conversation