topic Re: SAS data to excel in ODS and Base Reporting

SAS data to excel

yael — Wed, 13 Apr 2016 05:49:20 GMT

Hello expert,

I tried to handle it by reading and trying a lot but NO success. I want to export my output sas data to excel, the answer I got in log is

Physical file does not exist and I do not understand the problem. I am using SAS 9.2.

The program is:

ODS CSV FILE='C:\Temp\family.CSV';

proc means data=sasuser.sasfile120416;

var dp dq ee dr;

run;

ODS CSV CLOSE;

I appreciate any help,

Thanks a lot

Re: SAS data to excel

Kurt_Bremser — Wed, 13 Apr 2016 06:30:26 GMT

Please post the relevant portion of the log.

What is your SAS setup?

University Edition, SAS On Demand, or Licensed?

Single machine, or client/server?

If C/S, which server operating system?

Gosh, just read it, you're on 9.2, so it can't be UE or On Demand. The other questions are still relevant, though.

Re: SAS data to excel

yael — Wed, 13 Apr 2016 07:39:32 GMT

I am a student and this is SAS via the universty, My operating server is Windows 8.1

I am new in SAS so maybe it sounds like stupid question, but maybe the result is to save the data of the "result window" in WORK library and then to export it to EXCEL file, NO?

Thanks for any comments 🙂

Re: SAS data to excel

Kurt_Bremser — Wed, 13 Apr 2016 07:47:09 GMT

I'm still missing the log 😉

Since that is the main source for debugging, I can't be of much help without it.

Re: SAS data to excel

yael — Wed, 13 Apr 2016 07:52:43 GMT

please...

1 ODS CSV FILE='C:\Temp\family.CSV';

NOTE: Writing CSV Body file: C:\Temp\family.CSV

ERROR: Physical file does not exist, C:\Temp\family.CSV.

WARNING: No body file. CSV output will not be created.

2 proc means data=sasuser.sasfile120416;

3 var dp dq ee dr;

4 run;

NOTE: There were 8392 observations read from the data set SASUSER.SASFILE120416.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.06 seconds

cpu time 0.03 seconds

5 ODS CSV CLOSE

Re: SAS data to excel

Kurt_Bremser — Wed, 13 Apr 2016 07:56:11 GMT

OK, so it's verified that not only your client, but your server runs on Windows. Otherwise the ERROR message would have contained parts of a UNIX file path.

So the most likely scenario is that your SAS runs on a remote server that naturally can't see your C:\temp, as this is local to your desktop computer. Am I guessing right?

Re: SAS data to excel

yael — Wed, 13 Apr 2016 08:14:01 GMT

"Am I guessing right?"

The answer: Yes 😞

Re: SAS data to excel

yael — Wed, 13 Apr 2016 08:15:23 GMT

But I also have to say that I succeed to import excel file to SAS

Re: SAS data to excel

Kurt_Bremser — Wed, 13 Apr 2016 08:21:36 GMT

Then let's dig deeper into the setup and what possibly happens:

Your client software (most likely Enterprise Guide) can see your local drive(s) and does handle all the transfer stuff for you. Importing and exporting with EG tasks works well.

Your SAS code runs exclusively on the server, which does not have access to your local drive(s), and so you can't do import/export strictly from code as you would have believed it to be.

Remedy one: keep using the EG tasks for import/export

Remedy two: see if there is a shared network resource available that both your desktop and the SAS server have access to, and store your Excel files there

Remedy three: Use a file transfer tool like WinSCP to transfer files from your desktop to the server.

Re: SAS data to excel

yael — Wed, 13 Apr 2016 08:24:15 GMT

ok, I will check it and BIG BIG THANKS !smileyhappy: