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.
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!
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.