topic Proc compare - replacing missing as 100 in SAS Programming

Proc compare - replacing missing as 100

SAS_prep — Thu, 16 May 2019 13:31:35 GMT

Hello,

i have two datasets and i need to get variance between two dataset so i have used proc compare. I need missing values in the output to be changed to 100 in my output when either one of the input has a value and other one has zero or missing. The data i have mentioned here is sample one. Rows and columns will be more in actual data.

/**Dataset 1**/

Data DT1;
input v_id $ @4 month1 @7 month2 @10 month3;
datalines;
aa 12 58 0 
bb 34 47 . 
cc 25 .  54
;
run;

/**Dataset 2**/

Data DT2;
input v_id $ @4 month1 @7 month2 @10 month3;
datalines;
aa 62 58 3 
bb 34 47 . 
cc 15 0  54
dd 15 12 14
ee 8  9  65
;
run;

/** Compare***/

Proc compare base=dt1
compare=dt2 OUTPERCENT
out=result noprint;
ID V_Id;
VAR _NUMERIC_;
run;

/**Output from proc compare**/
_TYPE_	_OBS_	v_id	month1	month2	month3
PERCENT	1	aa	416.6666667	0	.
PERCENT	2	bb	0	0	.
PERCENT	3	cc	-40	.	0

/**expected output**/

_TYPE_	_OBS_	v_id	month1	month2	month3
PERCENT	1	aa	416.6666667	0	100
PERCENT	2	bb	0	0	.
PERCENT	3	cc	-40	.	0

Re: Proc compare - replacing missing as 100

Jagadishkatam — Thu, 16 May 2019 13:57:38 GMT

Hope the below code will help

Data DT1;
input v_id $ @4 month1 @7 month2 @10 month3;
datalines;
aa 12 58 0 
bb 34 47 . 
cc 25 .  54
;
run;

/**Dataset 2**/

Data DT2;
input v_id $ @4 month1 @7 month2 @10 month3;
datalines;
aa 62 58 3 
bb 34 47 . 
cc 15 0  54
dd 15 12 14
ee 8  9  65
;
run;


data all;
merge DT1(in=a) DT2(in=b rename=(month1=_month1 month2=_month2 month3=_month3));
by v_id;
run;

data want;
set all;
array var1(*) month1 month2 month3;
array var2(*) _month1 _month2 _month3;
do i = 1 to dim(var1);
if var1(i)=0 then var1(i)=.;
if var2(i)=0 then var2(i)=.;
if nmiss(var1(i),var2(i))=1 then do; var1(i)=coalesce(var1(i),100); var2(i)=coalesce(var2(i),100); end;
end;
run;

Re: Proc compare - replacing missing as 100

SAS_prep — Mon, 20 May 2019 14:53:11 GMT

Hi jag,

i am not getting the right result. The value v_id for month3 in DT1 is 0 and in DT2 it is 3. for this combination my output should have 100 as variance.