topic Re: Any help to get expected results ? in SAS Programming

Any help to get expected results ?

GPatel — Sun, 10 Oct 2021 01:49:34 GMT

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;

Re: Any help to get expected results ?

Patrick — Sun, 10 Oct 2021 02:52:40 GMT

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;

Re: Any help to get expected results ?

Ksharp — Sun, 10 Oct 2021 10:12:58 GMT

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;

Re: Any help to get expected results ?

GPatel — Wed, 13 Oct 2021 10:29:25 GMT

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