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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.