I need help with the following accomplishing the task below based on the following criteria:
1) if B_value='C' then sub_value=value
2) if B-value not equal 'C', assign sub_value based on the following. If they have a common C-ID, assign the value of (B_value ='C' and start code='O' and living_code='A') to the other. For instance, in the first 4 data observation with a C_ID of 1, the sub_value for B11, C11 and D11 will be A11.
3) Do not assign (B-value ='C' and start_code='O' and living_code='D') to dependent values sharing the same C-ID. Instead use the value of B-value=C and start-code=S and living_code =A. For instance, where C-ID=3, the sub values of the other B-value=D, will
be B33
I'll prefer the data set method as supposed to proc sql but I'm okay with either approach
Thanks
DATA have
B_value C_ID Stat_code Living_code value sub_value
C 1 O A A11
D 1 z A B11
D 1 z A C11
D 1 z A D11
C 2 O A A22
C 2 S A B22
C 2 S A C22
C 2 S A D22
D 2 z A E22
D 2 z A F22
C 3 O D A33
C 3 S A B33
D 3 z A C33
D 3 z A D33
D 3 z A E33
Data Want
B_value C_ID Stat_code Living_code value sub_value
C 1 O A A11 A11
D 1 z A B11 A11
D 1 z A C11 A11
D 1 z A D11 A11
C 2 O A A22 A22
C 2 S A B22 B22
C 2 S A C22 C22
C 2 S A D22 D22
D 2 z A E22 A22
D 2 z A F22 A22
C 3 O D A33 A33
C 3 S A B33 B33
D 3 z A C33 B33
D 3 z A D33 B33
D 3 z A E33 B33
What do mean by "share the same C-ID"? Are you matching the data set agains other rows in the same data set? If so, which ones?
Take a look at the 4 observation below. They all have a C-ID value of 1. That's what i mean by "share the same C-ID
B_value C_ID Stat_code Living_code value sub_value
C 1 O A A11
D 1 z A B11
D 1 z A C11
D 1 z A D11
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.