topic Re: Highlighting Data Set Changes in Excel Output in SAS Procedures

Highlighting Data Set Changes in Excel Output

honk — Mon, 14 Sep 2015 20:40:14 GMT

Hi All,

I have a database table that becomes updated every so often with new information. For example in an earlier version of the database a person's name might be misspelled or an address typed incorrectly. When the database table is updated, I want to be able to export the table with the new values highlighted in an excel sheet. Is this something that is possible to do? I have used proc compare to output a pdf of changes in variables values, and while this is also helpful, I was hoping the excel sheet could be done so that others I work with can easily keep using the entire database table to continue their work and just know that a value has changed from the last version.

Thank you for any help.

Re: Highlighting Data Set Changes in Excel Output

data_null__ — Mon, 14 Sep 2015 21:27:23 GMT

You can do this using the information from PROC COMPARE OUT= to indicate which variables/obs are changed. This is used in PROC REPORT with CALL DEFINE to highlight the value. Post some example data makes it easier for all to see what you want.

Re: Highlighting Data Set Changes in Excel Output

honk — Mon, 14 Sep 2015 21:54:58 GMT

Thank you for the response data_null__ . I was exploring PROC COMPARE OUT= and noticed that it gave X'd out values, periods, and 0s. So I could basically tell PROC REPORT and CALL DEFINE to use the 'OUT=' data set as a map basically to highlight which values are new?

Good idea for posting data, I am pasting a fictitious example below:

Original Table
First_Name	Last_Name	Address	City	State	Zip
Bob	Newman	877 Walnut St	Blue	TX	99988
Christopher	Watkins	222 Chestnut Dr	Red	WI	77788
Sally	Fields	453 Peanut Ln	White	CA	22233
Winona	Rider	991 Pistachio Ct	Green	GA	55587

Updated Table
First_Name	Last_Name	Address	City	State	Zip
Robert	Newman	877 Walknot St	Blue	TX	99988
Christopher	Watkins	222 Chestnut Dr	Red	WI	88888
Sally	Ride	453 Peanut Ln	White	MI	22233
Winona	Ryder	991 Pistachio Ct	Purple	GA	55587

***Blue/italicized values would be highlighted in excel output. All original data would be kept in the output, except for updated values.

Re: Highlighting Data Set Changes in Excel Output

andreas_lds — Wed, 16 Sep 2015 07:24:25 GMT

Use proc compare with out=work.Differences and noprint. Differences are marked by X in char variables, and a value > 0 in numeric variables.
Knowing this, you can create a new dataset containing flag-variables:

flag_First_Name = (findc(First_Name, 'X') > 0);
/* ... */
flag_Zip = (Zip ^= 0);

Drop the variables created by proc compare, except _obs_.
Add the Flag-Dataset to the dataset containing the new values, example:

data work.UpdateExtended;
   set work.DifferencesExtended ;
   set work.Update point=_obs_;

   drop _obs_;
run;

proc report with a compute-block creates the output:

proc report data=work.UpdateExtended;
   columns First_Name Last_Name Address City State Zip flag: dummy;

   define First_Name / display; 
   define Last_Name / display;
   define Address / display;
   define City / display;
   define State / display;
   define Zip / display;
   define flag_First_Name / noprint;
   define flag_Last_Name / noprint;
   define flag_Address / noprint;
   define flag_City / noprint;
   define flag_State / noprint;
   define flag_Zip / noprint;
   define dummy / computed noprint;

   compute dummy / char;
      array vars [6] $ _temporary_ ("_c1_" "_c2_" "_c3_" "_c4_" "_c5_" "_c6_");
      array flags [6] _c7_ _c8_ c9_ _c10_ _c11_ _c12_;

      do i = 1 to dim(flags);         
         if flags[i] then call define(vars[i], "style", "style=[background=yellow]");
      end;

   endcomp;
run;

One problem remains: creating large (>30k obs) excel-files with ods excel is not possible.

Re: Highlighting Data Set Changes in Excel Output

honk — Thu, 17 Sep 2015 19:02:19 GMT

Thank you, looks promising. I am going to try this out soon and see if I can get it to work

@andreas_lds wrote:
Use proc compare with out=work.Differences and noprint. Differences are marked by X in char variables, and a value > 0 in numeric variables.
Knowing this, you can create a new dataset containing flag-variables:
flag_First_Name = (findc(First_Name, 'X') > 0);
/* ... */
flag_Zip = (Zip ^= 0);
Drop the variables created by proc compare, except _obs_.
Add the Flag-Dataset to the dataset containing the new values, example:
data work.UpdateExtended;
   set work.DifferencesExtended ;
   set work.Update point=_obs_;

   drop _obs_;
run;
proc report with a compute-block creates the output:
proc report data=work.UpdateExtended;
   columns First_Name Last_Name Address City State Zip flag: dummy;

   define First_Name / display; 
   define Last_Name / display;
   define Address / display;
   define City / display;
   define State / display;
   define Zip / display;
   define flag_First_Name / noprint;
   define flag_Last_Name / noprint;
   define flag_Address / noprint;
   define flag_City / noprint;
   define flag_State / noprint;
   define flag_Zip / noprint;
   define dummy / computed noprint;

   compute dummy / char;
      array vars [6] $ _temporary_ ("_c1_" "_c2_" "_c3_" "_c4_" "_c5_" "_c6_");
      array flags [6] _c7_ _c8_ c9_ _c10_ _c11_ _c12_;

      do i = 1 to dim(flags);         
         if flags[i] then call define(vars[i], "style", "style=[background=yellow]");
      end;

   endcomp;
run;
One problem remains: creating large (>30k obs) excel-files with ods excel is not possible.