BookmarkSubscribeRSS Feed
Calcite | Level 5

Good morning!


I have to two excel files, the first one is really big, full of data and the second one is like a compendium of the data for publications. All the data in the second one comes from a range of data in the first one.


Which is the best way/aproach to do a program that i load the first excel, run the code and get the second one updated


One example is:

In the target excel i have to update for example the second column from obs 10 to 19 and this data comes from the fifth table, sixth column from obs 50 to 59.


Left one is the target excel to update, right one the base excel


Sin título.png



Thanks in advance!

Super User

First of all, export the data to a reasonable format that allows you to keep full control over data structures when importing into SAS.

CSV recommended, but TSV or similar is also OK.

Importing via Excel files involves a lot of guessing by SAS, and does not lead to consistent results. Very often you need to modify the data afterwards, so it does not save you any work.

Once you have successfully imported the data into SAS, use the macro provided in to convert your SAS datasets to data steps, which can be posted here, using the {i} or "little running man" icons for posting code.

If you have problems importing the data, post the csv's as attachment or with the {i} icon.


Posting screenshots of data is NOT helpful, as it would force us to type data off the screen, and in this case (Excel) does not give us ANY clue about data structures (types, formats).



Diamond | Level 26 RW9
Diamond | Level 26

Well, Excel is a very poor data format.  My suggestion would be to do the coding directly in Excel using VBA.  Why, well there are several benefits.  First, to get the data into SAS you may well have to jump through several hoops - as I mention before Excel is a particularly bad data format with no real structure or control.  So you first need to get the data into SAS, then code to do your operation, and then get it out again.  If you do the coding directly in Excel using VBA the only tech you are using is Excel, saves import/export.  You may, depending on the complexity of your problem - which I can't really tell from a picture - get away with just using a lookup function and drag that across cells, however this is not within the realms of a SAS forum, so try Google as there is always millions of hits:

Super User

You can use the macro here to export your data to a specific range in your Excel Sheet:



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
  • 3 replies
  • 4 in conversation