Help using Base SAS procedures

Highlighting Data Set Changes in Excel Output

Reply
Occasional Contributor
Posts: 14

Highlighting Data Set Changes in Excel Output

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.

Respected Advisor
Posts: 3,799

Re: Highlighting Data Set Changes in Excel Output

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.
Occasional Contributor
Posts: 14

Re: Highlighting Data Set Changes in Excel Output

Posted in reply to data_null__

Thank you for the response

 

 

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

 

Original Table 
First_NameLast_NameAddressCityStateZip
BobNewman877 Walnut StBlueTX99988
ChristopherWatkins222 Chestnut DrRedWI77788
Sally Fields453 Peanut LnWhiteCA22233
WinonaRider991 Pistachio CtGreenGA55587
      
Updated Table 
First_NameLast_NameAddressCityStateZip
RobertNewman877 Walknot StBlueTX99988
ChristopherWatkins222 Chestnut DrRedWI88888
Sally Ride453 Peanut LnWhiteMI22233
WinonaRyder991 Pistachio CtPurpleGA55587

 

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

Super Contributor
Posts: 345

Re: Highlighting Data Set Changes in Excel Output

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

 

Occasional Contributor
Posts: 14

Re: Highlighting Data Set Changes in Excel Output

Posted in reply to andreas_lds

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

 

 


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.

 


 

 

Ask a Question
Discussion stats
  • 4 replies
  • 505 views
  • 2 likes
  • 3 in conversation