topic Re: how to combine only when the value is missing in SAS Programming

how to combine only when the value is missing

septemberbulb — Tue, 24 Jan 2017 15:18:30 GMT

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.

Re: how to combine only when the value is missing

ballardw — Tue, 24 Jan 2017 15:27:26 GMT

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.

data new;

set origianl;

if code1='' and code2='' then Do;

code1=codeb1;

code2=codeb2;

end;

drop codeb1 codeb2;

/* or Keep application code1 code2;*/

run;

Re: how to combine only when the value is missing

collinelliot — Tue, 24 Jan 2017 15:34:37 GMT

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;

Re: how to combine only when the value is missing

collinelliot — Tue, 24 Jan 2017 15:45:55 GMT

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

Re: how to combine only when the value is missing

septemberbulb — Tue, 24 Jan 2017 16:03:16 GMT

THANK YOU VERY MUCH.

Re: how to combine only when the value is missing

septemberbulb — Tue, 24 Jan 2017 16:03:37 GMT

thank you

Re: how to combine only when the value is missing

anoopmohandas7 — Tue, 24 Jan 2017 16:13:57 GMT

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 ;

Re: how to combine only when the value is missing

septemberbulb — Tue, 24 Jan 2017 18:21:19 GMT

thank you