topic Re: Transferring sheets from one excel file to another in SAS Programming

Transferring sheets from one excel file to another

SP_SAS — Wed, 22 Jan 2020 22:09:37 GMT

I have two .xlsx files that both have data and images. I want to combine the sheets of both the excel files into one.

I did a simple thing like

libname a xlsx "myfile1.xlsx"; (has two sheets sheeta1 sheeta2)

libname b xlsx "myfile2.xlsx"; (has two sheets sheetb1 sheetb2)

data a.sheetb1;

set b.sheetb1;

run;

data a.sheetb2;

set b.sheetb2;

run;

This copies the data fine but does not copy the images. the final file myfile1.xlsx has 4 sheets.

How can I keep the images also when I do this kind of copy.

If SAS can't do it. Can Python do it?

Thanks

Re: Transferring sheets from one excel file to another

Patrick — Wed, 22 Jan 2020 22:41:58 GMT

If that's a one-off task then I'd do this manually as it's really simple and quick using Excel.

I don't believe there is a straightforward way using SAS other than calling Windows/Excel to do the job for you (which means the process needs to execute in an environment where Excel is installed).

It looks like the Python Pandas package would allow you to do such a 1:1 copy of a sheet. Not sure though if this still requires Excel installed on the same machine and Pandas is just calling these Excel components internally.

https://stackoverflow.com/questions/44593705/how-to-copy-over-an-excel-sheet-to-another-workbook-in-python

import pandas as pd

#change xxx with the sheet name that includes the data
data = pd.read_excel(sourcefile, sheet_name="xxx")

#save it to the 'new_tab' in destfile
data.to_excel(destfile, sheet_name='new_tab')

Re: Transferring sheets from one excel file to another

SP_SAS — Wed, 22 Jan 2020 22:51:59 GMT

This will not copy the image files though..

Re: Transferring sheets from one excel file to another

Patrick — Wed, 22 Jan 2020 23:05:54 GMT

@SP_SAS

OK, then it appears the same answer I've given for SAS also applies for Python unless someone wrote a package which does the copying the way you need it - but that's then likely the same like finding somewhere an already written SAS macro which does such a copy. I guess both would need to call Windows/MS Office components.

Re: Transferring sheets from one excel file to another

Reeza — Wed, 22 Jan 2020 23:06:28 GMT

Then use VBS and call that VBS from Python or SAS.

Re: Transferring sheets from one excel file to another

SASKiwi — Wed, 22 Jan 2020 23:24:14 GMT

Behind the scenes SAS is importing the data from one sheet in your source workbook then writing that data out to a sheet in your target workbook. Anything that can't be stored in a SAS dataset like images wont be included.