BookmarkSubscribeRSS Feed
honk
Obsidian | Level 7

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.

4 REPLIES 4
data_null__
Jade | Level 19
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.
honk
Obsidian | Level 7

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.

andreas_lds
PROC Star
  • 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.

 

honk
Obsidian | Level 7

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.

 


 

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1847 views
  • 2 likes
  • 3 in conversation