BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ccaudillo100
Obsidian | Level 7

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?

ccaudillo100_1-1657656671372.png

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
10 REPLIES 10
ballardw
Super User

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.

ccaudillo100
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ccaudillo100
Obsidian | Level 7

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;


 

Sajid01
Meteorite | Level 14

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.

ccaudillo100
Obsidian | Level 7

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

Sajid01
Meteorite | Level 14

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.

ccaudillo100
Obsidian | Level 7

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."

Sajid01
Meteorite | Level 14

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...

ccaudillo100
Obsidian | Level 7
I will look into it! Thank you

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1645 views
  • 2 likes
  • 4 in conversation