BookmarkSubscribeRSS Feed
SAS_prep
Calcite | Level 5

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


 

 

2 REPLIES 2
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
SAS_prep
Calcite | Level 5

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 619 views
  • 0 likes
  • 2 in conversation