🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Pyrite | Level 9

## Any help to get expected results ?

I have two datasets, e and f.

data e;
input cycle tot1 tot2;
cards;
205 10 30
205 20 40
;
run;

data f;
infile cards missover;
input cycle tot1 ;
cards;
205 .
205 50
;
run;

The expected dataset would be:

cycle tot1 tot2
205 10 30
205 50 40

>>>>>

Test code :

data g;
merge e(in=a) f(rename=(tot1=t1 tot2=t2));
by cycle;
array t(2) t1-t2;
array tot(2) tot1-tot2;
do i=1 to 2;
if t(i)^= . then tot(i)=t(i);
end;
if a ;
drop t1 t2 i;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Any help to get expected results ?

```data e;
input cycle tot1 tot2;
n+1;
cards;
205 10 30
205 20 40
;

data f;
infile cards missover;
input cycle tot1;
n+1;
cards;
205 .
205 50
;

data want;
update  e f;
by cycle n;
drop n;
run;```
3 REPLIES 3
Opal | Level 21

## Re: Any help to get expected results ?

Below building on the code you provided.

``````data e;
input cycle tot1 tot2;
cards;
205 10 30
205 20 40
;

data f;
infile cards missover;
input cycle tot1;
cards;
205 .
205 50
;

/* option 1: coalesce() will pick the first non-missining value */
data g_1;
merge e(in=a) f(rename=(tot1=_tot1));
tot1=coalesce(_tot1,tot1);
drop _tot1;
run;

/* option 2: if _tot1 not missing then use it to populate tot1 */
data g_2;
merge e(in=a) f(rename=(tot1=_tot1));
if _tot1 ne . then tot1=_tot1;
drop _tot1;
run;
``````
Super User

## Re: Any help to get expected results ?

```data e;
input cycle tot1 tot2;
n+1;
cards;
205 10 30
205 20 40
;

data f;
infile cards missover;
input cycle tot1;
n+1;
cards;
205 .
205 50
;

data want;
update  e f;
by cycle n;
drop n;
run;```
Pyrite | Level 9

## Re: Any help to get expected results ?

Patric and KSharp:

Thanks to both of you for providing excellent solution. I appreciate your time and interest and service to SAS Community.

Regards,

GPatel

Discussion stats
• 3 replies
• 492 views
• 1 like
• 3 in conversation