Hi Professionals,
I created a simple sample for my tables.
the original one is like:
APPLICATION | CODE1 | CODE2 | CODEB1 | CODEB2 |
1111 | F | S | F | S |
1112 | N | N | ||
1113 | CF | CF | ||
1114 | B<40 | |||
1115 | F | S | ||
1116 | F |
my goal is to create:
APPLICATION | CODE1 | CODE2 |
1111 | F | S |
1112 | N | |
1113 | CF | |
1114 | B<40 | |
1115 | F | S |
1116 | F |
For each application, when code1 ='' and code2='' let code1=codeb1 and code2=codeb2. for the rest code 1 or code2, just keep their original value.
then delete codeb1 and codeb2.
How can I do the merge?
data new;
set origianl;
if code1='' and code2='' then code1=codeb1 and code2=codeb2;
else (here I dont' know how to describe let codes = original value);
run;
thank you for anyone who helps.
You are actually most of the way there. What you are missing in concept is if the values are not both missing then you are actually saying "do nothing, leave it alone". However you can only have one assignment per statement so you have to assign the values of code1 and code2 separately. To make them respond to the same condition then use a DO; End; block to group them.
You want to drop the extra variables or alternatively use KEEP to keep the ones you want.
So
data new;
set origianl;
if code1='' and code2='' then Do;
code1=codeb1;
code2=codeb2;
end;
drop codeb1 codeb2;
/* or Keep application code1 code2;*/
run;
You are actually most of the way there. What you are missing in concept is if the values are not both missing then you are actually saying "do nothing, leave it alone". However you can only have one assignment per statement so you have to assign the values of code1 and code2 separately. To make them respond to the same condition then use a DO; End; block to group them.
You want to drop the extra variables or alternatively use KEEP to keep the ones you want.
So
data new;
set origianl;
if code1='' and code2='' then Do;
code1=codeb1;
code2=codeb2;
end;
drop codeb1 codeb2;
/* or Keep application code1 code2;*/
run;
I think my solution works with the data, but not with the description. The above addresses the problem described better, I think.
I'd just use the coalesce function to get what you want:
data have;
infile datalines dsd;
input APPLICATION CODE1 $ CODE2 $ CODEB1 $ CODEB2 $;
datalines;
1111, F, S, F, S
1112, N, , N,
1113, CF, , CF,
1114, , , B<40,
1115, , , F, S
1116, , , F,
;
data want;
set have;
code1 = coalescec(code1, codeb1);
code2 = coalescec(code2, codeb2);
run;
Use a CASE statement as show in below sample code.
proc sql;
select
case
when CODE1 = ' ' then CODEB1 else CODE1 end as CODE1
from test ;
quit ;
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.