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.
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...
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.
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
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.
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;
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.
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
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.
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."
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: