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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 649 views
  • 0 likes
  • 2 in conversation