opening an excel file with SAS

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

opening an excel file with SAS

Hi,

 

I found the following macro to open an already existing Excel file:

 

%MACRO Sleep(pSeconds);
DATA _NULL_;
vVar1=SLEEP(&pSeconds);
RUN;
%MEND;

 

/* Macro to open Excel and an Excel file */
/* Parameters:
pFile: file name with path and without extension */
%MACRO OpenXls(pFile);
OPTIONS NOXWAIT NOXSYNC MISSING='';
X "C:\Archiv~1\micros~2\Office10\excel.exe / &pFile..xls";
%sleep(2);
%MEND;

 

In my case pFile = C:\yearly_rate  (and the extension is xls). 

But when I ran the code the prompt command opens and closes instantly and the file doesn't open. I tried putting the extension of my Excel:

C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office

but still don't get a result...

 

Could you please help me find the error in my code?

 

Thank you!

 

 

ps: I use SAS 9.3


Accepted Solutions
Solution
‎04-04-2016 10:13 PM
Super User
Super User
Posts: 6,322

Re: opening an excel file with SAS

[ Edited ]

If you have spaces in your file names then you need to add quotes around them or else the command shell will think the spaces mark the end of the command or file name.  The X command will use the outer quotes to find string to send as the command so you need to add more quotes around the command and/or filename.

You could use single quotes to quote the command that you are gving to the X command.

X '"C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"'; 

If you want to use double quotes around the outside then you need to double the inside ones.

X """C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"""; 

You can use the QUOTE() function to make it easier.

X %sysfunc(quote("C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls")); 

 

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,198

Re: opening an excel file with SAS

Not sure if I can help you with the specific problem.
But what are you trying to achieve and why?
Data never sleeps
Super Contributor
Posts: 413

Re: opening an excel file with SAS

Hi,

 

I actually found a paper about SAS macros : http://www2.sas.com/proceedings/forum2007/059-2007.pdf

 

Some of the macros seem interesting to me and I could use them, but as very often happens I encountered problems at the very first step...

 

Thanks!

Grand Advisor
Posts: 17,325

Re: opening an excel file with SAS

Your path to the Excel application seems incorrect. The Excel application is usually found under C:\program files\ not your user path. 

 

You can turn on the xwait/xsync commands to see the command line interface and if it generates an error there. 

 

Why are you trying to use DDE and an Xls file? Xls files are pretty outdated ... At least 6 years old now. 

 

 

Super Contributor
Posts: 413

Re: opening an excel file with SAS

[ Edited ]

Hi,

 

I ctually found a paper with SAS macros for Excel and some of them can be very helpful for me:

http://www2.sas.com/proceedings/forum2007/059-2007.pdf

 

I think that I found the path to my Excel, but when I try opening my file it still doesn't open. Even just opening Excel doesn't work, here is the code that I use just to try to open Excel: 

%MACRO Sleep(pSeconds);
DATA _NULL_;
vVar1=SLEEP(&pSeconds);
RUN;
%MEND;


/* Macro to open Excel */
%MACRO Open_Xls();
OPTIONS NOXWAIT NOXSYNC MISSING='';
X "C:\Program Files\Microsoft Office\Office14\excel.exe";
%sleep(2);
%MEND;

 

But again, a very fast opening and closing of command prompt.

 

I use Xls becasue I work on several computers and some of them don't have newer versions of Excel...

 

 

Thank you! 

Grand Advisor
Posts: 17,325

Re: opening an excel file with SAS

What's the problem you're trying to solve with the code above? 

Dynamic Data Exchange (DDE) is outdated, and generally not recommended. 

 

If you're trying to get data to excel consider using one of the following methods instead:

 

Proc Export - DBMS=XLSX allows for many options, especially in the latest SAS release

ODS Excel - generates native XLXS files

ODS Tagsets.excelxp

A Poor/Rich SAS Users Proc Export Macro (http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export) 

 

If you explain your problem, you'll get better solutions. 

 

If you really want to continue with DDE, read your code line by line. You'll see the line below:

 

OPTIONS NOXWAIT NOXSYNC MISSING='';

Change it so you remove the NO portion so that you can see the pop up window, which is the DOS/terminal window. 

I recommend reading the documentation so you know what each of those options is doing. Running code you don't understand can be a quick way to making big mistakes. 

 

Additionally, you can just run the command below, in a brand new SAS session, to see if Excel opens.

 

X "C:\Program Files\Microsoft Office\Office14\excel.exe";
Super Contributor
Posts: 413

Re: opening an excel file with SAS

Hi,

 

Basically I have many files in a folder, and each file has two columns: a variable y and variable x. What I would like to do is for each file to make a regression and plot the y vs x, and then resend the folder by email.

 

Also, I figured that Excel can be opened like this: X "start excel";

My test file can also be accessed: X "C:\yearly_rate.xls";

 

But when I try to open my real file which is in a folder: X "C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"; I get an error message that 'C:\Documents' is not recognized as an internal or external command, operable program or batch file.

 

Thank you!

Solution
‎04-04-2016 10:13 PM
Super User
Super User
Posts: 6,322

Re: opening an excel file with SAS

[ Edited ]

If you have spaces in your file names then you need to add quotes around them or else the command shell will think the spaces mark the end of the command or file name.  The X command will use the outer quotes to find string to send as the command so you need to add more quotes around the command and/or filename.

You could use single quotes to quote the command that you are gving to the X command.

X '"C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"'; 

If you want to use double quotes around the outside then you need to double the inside ones.

X """C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"""; 

You can use the QUOTE() function to make it easier.

X %sysfunc(quote("C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls")); 

 

Occasional Contributor
Posts: 9

Re: opening an excel file with SAS

Hi Friends,

 

Please advice to open an existing exel file using SAS in a cloud environment.

 

Thanks..

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: opening an excel file with SAS

Hi,

 

Please don't re-open an old thread for a new question.  Start a new thread, ensure you post what version/type of SAS you are using, what Office you have availabel etc.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: opening an excel file with SAS

I would agree with @Reeza here.  DDE is not a good approach, in some circumstances it doesn't work at all.  If its simply importing a file, use proc import.  However, why do you have many Excel files, with a tiny bit of data in each?  It sounds to me like the first thing which has gone wrong in your problem here is the data transfer.  Where does the data come from, i.e. why can they not put it in an appropriate format (not just why are they using Excel which is not good to start with, but why the many files, why not one file with an additional column).  It just looks like whoereve supplied the file has gone out their way to make the data hard to access.

Grand Advisor
Posts: 17,325

Re: opening an excel file with SAS

You do not need to open Excel.

 

You do need to import the data from all of the files, run your regression, obtain your output and send that by email. 

 

1. PROC IMPORT

2. PROC REG

3. PROC SGPLOT

 

If you need to do it in Excel, then I suggest writing a VB macro rather than SAS because that's pretty much what you'd be doing anyways, except with DDE you're stuck using VB 4.0 code rather than the modern day VB code available.

 

 

Super Contributor
Posts: 413

Re: opening an excel file with SAS

Hi,

 

Strictly speaking, by adding additional quotes ' ' I managed to open my Excel file with command prompt, so this would answer my question the way that I formulated it in the description.

 

But in reality it is much wiser to get the data into SAS and then work on it, instead of working in Excel and use SAS as an intermediary.

 

Great thanks to all !!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1614 views
  • 8 likes
  • 6 in conversation