OK, I picked this up again today, and I think I finally figured this out. One thing I was not aware of (and please, let me know if I am off base here) is that proc export CREATES the excel file for you on that first run (I thought the excel file had to be there prior to running the proc export). This created file is what you can use as a shell for a template, so you can add new tabs for calculations, formatting, etc. Subsequent runs of proc export on the same program will REPLACE the data for the tabs that are getting exported. So what I was doing wrong was that I created the excel file first (for this exercise, the excel file needed to have 2 tabs with exported SAS data called "data" and "claims", plus one more where I make calculations based on data from "data" and "claims"). So when I run proc export, sas tried to create "data" and "claims" but they have been already created. Therefore SAS creates "data1" and "claims1" and puts the data there. The solution to this issue is to have SAS create that shell excel file first. After that do the necessary manipulations you want in this newly created excel file, and then rerun the sas program, and the proc export will REPLACE the data in the tabs being exported, and the tab with calculations based on "data" and "claims" tabs and formatting are updated accordingly. Thanks!
... View more