Excel Moving formulas when adding additional fields to data table

[cbrotz]

I have inherited a group of Excel spreadsheets that read SAS Data tables created by SAS EG and linked through the SAS Addin.  These Excel workbooks are changed often.  Every time we add an additional field, it moves all the formulas around in the spreadsheet and we need to re organize and match them to the right headings etc.  Has anyone had this issue or found a solution?  Thank you!

[ballardw]

Usually the symptoms you describe are related to poor data model (adding columns) or less-than-ideal data interchange such as reliance on Proc Import and Export which may change the order of things when not watchec very closely.

One fix may be to do most of the analysis being done with formulae in Excel to SAS Procedures and the export the result.

Without more details you're going to get very generic device. Examples of what has been changing from period to period may help get more concrete recommendations.

[cbrotz]

I don't know about poor data modeling....this is pretty basic stuff.  We create a SAS datafile and connect through the SAS Addin.  There is no importing etc.  If I change my code to add a field to that SAS datafile, the Excel spreadsheet forumulas move all over.  The formulas are there, but they no longer line up with the headers that were put into the Excel spreadsheet.  There must be a way to lock the cells or something to keep this from happening.

[ballardw]

If you add variables (columns) to the SAS data set it is very likely that the add-in assumes the order from last time is the same but that is not the case.

Run:

Proc contents data=<yourdatasetgoeshere> nodetails order=varnum ;

run;

for one set that was working and one that has stuff shifting. See if the "Variable Creation Order" is the same.

It is likely that you need to create a subset of a data set so that the added variables aren't affecting things. If the new variables are needed in the report then that is a data modeling issue: structure of your data changing is modeling.

[cbrotz]

Ballardw - yes the varnum order is different for the two output files.  I changed the select statement in the proc sql to put them in the same order and it is lookig better....just one field off now.  There are other calculated fields in the spreadsheet and I am wondering if they have an effect on the order once it gets to the spreadsheet.  No I do not think anyone ever thinks of "what will happen if we need to add to this report".  Things change, products get added and dropped continually.  I am not sure what you are referring to as a "subset of data" ?  Can you elaborate on that and how it would make data changes easier?

Thank you