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
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;
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.