topic Re: Exporting to Excel in SAS Programming

Exporting to Excel

ccaudillo100 — Tue, 12 Jul 2022 20:58:27 GMT

Hello,

Currently I am able to export my SAS file to Excel when there is nothing else in the excel file.

If I try to create tables on new sheets from prior data exported from SAS and save it. The next time I run the program and then go to open the excel it will give me an error. I have other excels that I have done this with before and it gives me no issues. Any advice?

After I hit yes, then it tells me that someone else is in the file. When nobody else is using it and I had closed it prior.

Re: Exporting to Excel

ballardw — Tue, 12 Jul 2022 20:25:28 GMT

Please describe exactly how you are "exporting" data to Excel. Show the code if possible.

There are several approaches and some will lock a file not allowing you to access it until the process completes.

Others aren't really intended to modify existing Excel files.

Re: Exporting to Excel

ccaudillo100 — Tue, 12 Jul 2022 21:00:49 GMT

I have attached what I could show in the code on the original post. Sorry for so much cut out. It does import from and export to a network drive

Re: Exporting to Excel

PaigeMiller — Tue, 12 Jul 2022 21:58:28 GMT

Please include the code in your reply (not in an attachment) by clicking on the "running man" icon and pasting the code into the window that appears.

Also, please show us the ENTIRE log for this export (all of it for the export, every single line of the export code, do not show us parts of the log of the export and not show us other parts of the log of the export) by clicking on the </> icon and pasting the log into the window that appears.

Re: Exporting to Excel

Sajid01 — Tue, 12 Jul 2022 23:43:16 GMT

Using ODS excel should solve your issue.
This forum post has a solution for you

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-multiple-sheet/td-p/225377
The following refernces are also very helpful

https://www.lexjansen.com/nesug/nesug08/hw/hw01.pdf

https://www.mwsug.org/proceedings/2019/SP/MWSUG-2019-SP-072.pdf
You can google fro ODS excel and multisheet workbooks to find more resources.

Re: Exporting to Excel

ccaudillo100 — Wed, 13 Jul 2022 13:17:42 GMT

I am showing what I can due to confidentiality reasons. If you cannot help that is fine, I just figured I would see if anyone had helpful advice.

options validvarname=v7;

proc import datafile= 
“File"
out= work.calls21
dbms= xlsx replace;
run;

proc import datafile= 
“File"
out= work.visits21
dbms= xlsx replace;
run;

proc import datafile= 
“File"
out= work.calls22
dbms= xlsx replace;
run;

proc import datafile= 
“File"
out= work.visits22
dbms= xlsx replace;
run;

proc import datafile= 
“File"
out= work.CallsFS
dbms= xlsx replace;
run;

proc sql; 
create table CallsFS1 as 
select 

from CallsFS ;
quit;


Data CallsFS2 (drop= xx1);
set CallsFS1;
if xx then xx;
if xx then xx; 
xx  = (xx1*1);
 if xx = "." then xx = 0;
Run;





proc sql; 
create table Calls21A as 
select 

	
from calls21 ; 

quit;



data Calls22A;

	set calls22;
	rename;
run;



proc sql; 
create table Calls22B as 
select 
	
from calls22A ; 
quit;



proc sql; 
create table Visits21A as 
select 
	
from Visits21 ; 

quit;


proc sql; 
create table Visits22A as 
select 
	 
from Visits22 ; 
quit;






proc sort data= Calls21A; by xx; run;
proc sort data= Calls22B; by xx; run;
proc sort data= Visits21A; by xx; run;
proc sort data= Visits22A; by xx; run;
proc sort data= CallsFS2; by xx; run;

data Visits(DROP= NULL);
length;
Merge Visits21A Visits22A;
Run;


data Calls (DROP= NULL);
length 
Merge Calls21A Calls22B;
Run;

data CallsFS2 (DROP= NULL);
length;
Set CallsFS2;

Run;


data RawData ;
 Format 
Merge  Visits Calls CallsFS2;
by xx;
Run;

data RawData1;
set RawData;
;
 run;


proc export data=RawData1
	outfile="xx\Public\VisitCall"
	dbms=xlsx
	Replace;
	Sheet="Raw Data";
Run;


proc export data=Visits
	outfile="xx\Public\VisitCall"
	dbms=xlsx
	Replace;
	Sheet="Visits Dynamics";
Run;

proc export data=Calls
	outfile=" xx\Public\VisitCall"
	dbms=xlsx
	Replace;
	Sheet="Calls";
Run;


proc export data= CallsFS
	outfile="xx\Public\VisitCall"
	dbms=xlsx
	Replace;
	Sheet="CallsFSOG";
Run;

Re: Exporting to Excel

ccaudillo100 — Wed, 13 Jul 2022 13:32:29 GMT

I tried this and it deletes any new tabs/designs I created once it is ran again. I just need to be able to create a table that it exports to its designated tabs and leaves other tabs alone

Re: Exporting to Excel

Sajid01 — Wed, 13 Jul 2022 14:45:11 GMT

What I understand from your post is that whenever a data set is exported to an excel workbook, your previous sheets are gone.
I have taken the exports from your code and modified as follows. All the exported sheet be persist in the final.

filename myxlfile "/full_path_to_your_excel_file.xlsx";
proc export data=RawData1
	dbms=xlsx
	outfile=myxlfile Replace;
	Sheet="Raw Data";
Run;

proc export data=Visits
    dbms=xlsx
	outfile=myxlfile  
	Replace;
	Sheet="Visits Dynamics";
Run;

proc export data=Calls
    dbms=xlsx
	outfile=myxlfile  
	Replace;
	Sheet="Calls";
Run;

In case of an error please post the code and logs.

Re: Exporting to Excel

ccaudillo100 — Wed, 13 Jul 2022 18:10:30 GMT

It exported well the first time. But when I added a blank sheet in, saved, and closed it, I reran the code.

Unfortunately the excel is still giving me the pop up of

"We found a problem with some content in 'file.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, Click Yes."

Re: Exporting to Excel

Sajid01 — Wed, 13 Jul 2022 19:12:11 GMT

This typically does not happen when files are exported as xlsx.
Have a look at this https://answers.microsoft.com/en-us/msoffice/forum/all/file-is-corrupt-and-cannot-be-opened-in-excel-2016/3e378431-83c6-474e-a852-eaea3bb802a8

Re: Exporting to Excel

ccaudillo100 — Thu, 14 Jul 2022 14:00:11 GMT

I will look into it! Thank you