Link two excel files. Updates

[jbs23]

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

Thanks in advance!

[Kurt_Bremser]

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 https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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).

[RW9]

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:

https://stackoverflow.com/questions/31350851/updating-an-excel-worksheet-from-another-sheet-dependin...

[Reeza]

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

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export