BookmarkSubscribeRSS Feed
cbrotz
Pyrite | Level 9

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!

 

4 REPLIES 4
ballardw
Super User

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
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 709 views
  • 1 like
  • 2 in conversation