topic excel to SAS dataset in UNIX in Microsoft Integration with SAS

excel to SAS dataset in UNIX

maggi2410 — Fri, 30 Nov 2012 00:27:21 GMT

Hi ,

I have an excel file, which columns having length around 500-600 chars. I have to convert this to SAS dataset, in UNIX.

I am trying to use the excel libname with PCFILES statement, but it has one more argument , ie SERVER. Now I am not very sure, what should be its value.

I am running the code using XTERM. But the SAS Code and excel file are on some other drive, say U drive. If the server name is of U drive , then how could I find out its server name. Any thoughts?

Re: excel to SAS dataset in UNIX

SASKiwi — Fri, 30 Nov 2012 01:44:33 GMT

My understanding if you are trying to read Excel from Unix is that you must also have a Windows computer running the SAS PC File Server software along with MS Office installed. It is this computer that handles the conversion of Excel spreadsheets into a form that SAS understands.

If you are after a quick fix you could try saving the excel file into a CSV and then you should be able to import it without the above complications.

Re: excel to SAS dataset in UNIX

SandorSzalma — Fri, 30 Nov 2012 08:19:14 GMT

Hi,

you should give a try with PROC IMPORT.

It supports Export/Import with XLS, XLSX files on Unix Platfrom, too.

(SAS OnlineDoc: PROC IMPORT, Supported Data Sources and Environments)

Regards,

Sándor

Re: excel to SAS dataset in UNIX

Quentin — Fri, 30 Nov 2012 17:39:22 GMT

Agree with SASKiwi

server= is the name of the Windows server where the PC files server is running.

Re: excel to SAS dataset in UNIX

maggi2410 — Fri, 30 Nov 2012 19:13:08 GMT

Actually I dont have PC SAS. SAS is installed on other servers, and I submit the code via XTERM. Now, everytime I submit the code, I guess, whichever server is free, the code is submitted on that. So its random. What do you think in this case, can I still use PCFILES?

For now, I am converting the sheet into dab delimited file and then reading it. It is still truncating the columns , maximum length being 201. The code I am using is:

data temp.temp1 ;

infile '../test.txt' delimiter='09'x MISSOVER DSD lrecl=800 firstobs=3 ;

length chg_ref $800;

format _all_;informat _all_;

input chg_ref ;

run;

Do you think there is any problem in this. This UNIX system is getting on my nerves now.

Re: excel to SAS dataset in UNIX

SandorSzalma — Mon, 03 Dec 2012 08:36:26 GMT

does this U: drive point to a shared-filesystem on the Unix server, where SAS runs?

(Samba-Share?, U:\ -> SAS_Server:/xx/yy)

If yes, you can use PROC IMPORT without PC File Server, too.

Re: excel to SAS dataset in UNIX

TimArm — Thu, 06 Dec 2012 14:27:29 GMT

I also agree with SASKiwi; I cannot use proc import with Excel files on my AIX server.

If you are using a data step, you could try using the termstr=CRLF option on the infile statement - if your file was saved on a Windows platform.

SandorSzalma is correct that you can still use proc import on unix, as it can still be used to read text files (delimited).

HOWEVER, if you use proc import with a tab delimited file it only looks at the first 2 rows by default to guess variable lengths. You can change this by using the GUESSINGROWS= option.