DATA Step, Macro, Functions and more

how to combine only when the value is missing

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

how to combine only when the value is missing

Hi Professionals,

 

I created a simple sample for my tables.

the original one is like:

APPLICATIONCODE1CODE2CODEB1CODEB2
1111FSFS
1112N N 
1113CF CF 
1114  B<40 
1115  FS
1116  F 

my goal is to create:

APPLICATIONCODE1CODE2
1111FS
1112N 
1113CF 
1114B<40 
1115FS
1116F 

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: 11,343

Re: how to combine only when the value is missing

Posted in reply to septemberbulb

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;

View solution in original post


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

Re: how to combine only when the value is missing

Posted in reply to septemberbulb

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: 307

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: 28

Re: how to combine only when the value is missing

THANK YOU VERY MUCH.
PROC Star
Posts: 307

Re: how to combine only when the value is missing

Posted in reply to septemberbulb

 

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: 28

Re: how to combine only when the value is missing

Posted in reply to collinelliot
thank you
Contributor
Posts: 44

Re: how to combine only when the value is missing

Posted in reply to septemberbulb

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: 28

Re: how to combine only when the value is missing

Posted in reply to anoopmohandas7
thank you
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 166 views
  • 2 likes
  • 4 in conversation