Solved
Contributor
Posts: 29

# how to combine only when the value is missing

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.

Accepted Solutions
Solution
‎01-24-2017 11:03 AM
Super User
Posts: 13,528

## Re: how to combine only when the value is missing

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;

All Replies
Solution
‎01-24-2017 11:03 AM
Super User
Posts: 13,528

## Re: how to combine only when the value is missing

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;

PROC Star
Posts: 311

## Re: how to combine only when the value is missing

I think my solution works with the data, but not with the description. The above addresses the problem described better, I think.

Contributor
Posts: 29

## Re: how to combine only when the value is missing

THANK YOU VERY MUCH.
PROC Star
Posts: 311

## Re: how to combine only when the value is missing

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;``````
Contributor
Posts: 29

thank you
Contributor
Posts: 44

## Re: how to combine only when the value is missing

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 ;``````
Contributor
Posts: 29