Proc Compare

[jhh197]

Hi ,

I have two datasets test1 and test2 . I am comparing SD022 variable by City in both datasets whether counts match and what is the percent difference . I want to highlight proc compare output when the percent difference is higher .

In below for city Ocala and SD022 P1  Base is having 32045 records and Compare have 2205 there is huge difference and percentage difference is -96.15 . I want to highlight this using sas . Like this I need to run for SD024 ,SD023 and MD052 variables and generate one report and highlight percentages which are higher .

Obs	_TYPE_	_OBS_	City	SD022	COUNT	PERCENT
1	BASE	1	Ocala	P1	32045	4.5048
2	COMPARE	1	Ocala	P1	2205	4.8495
3	PERCENT	1	..	......	-96.15	-96.1495
4	BASE	2	Ocala	P2	16762	0.2128
5	COMPARE	2	Ocala	P2	16621	0.2108
6	PERCENT	2	..	......	-0.84	-0.9649
7	BASE	3	Ocala	P3	790663	10.0385
8	COMPARE	3	Ocala	P3	785776	9.964
9	PERCENT	3	..	......	-0.62	-0.7421

Can anyone please help ?

[ChrisNZ · Accepted solution]

By highlight, do you mean for example change colours or font weight?

It's possible for some procedures, but you need to alter the procedure default output format using proc template.

This example does that: it adds commas to the numbers generated by proc contents as strings.

proc fcmp outlib=WORK.FUNCS.GENERAL;
  function commastr( STR $ ) $ ;
    length A B $800 ; 
    A=STR;
    do until (A=B);
      B=A;
      A=prxchange('s/(\d)(\d\d\d(\Z|,))/\1,\2/',1,trim(B));  
    end;
    return (A);
  endsub;
run;

options cmplib=WORK.FUNCS;

proc format ;
  value $commastr low-high=[commastr()];
run;

%macro Format_Contents;
  ods path reset;
  ods path (prepend) WORK.TEMPLAT(update);

  proc template;
    edit BASE.CONTENTS.ATTRIBUTES;
      edit CVALUE2;
        format = $commastr800.;
      end;
    end;
    edit BASE.CONTENTS.ENGINEHOST;
      edit CVALUE1;
        format = $commastr800.;
      end;
    end;
  run;
%mend Format_Contents;

%Format_Contents;

proc contents data=SASHELP.CITIDAY;
run;

As for proc compare, it is different. See here.

[jhh197]

Thank you Chris for all your help .