excel to SAS dataset in UNIX

[maggi2410]

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?

[SASKiwi]

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.

[Quentin]

Agree with SASKiwi

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

[SandorSzalma]

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

[maggi2410]

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.

[SandorSzalma]

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.

[TimArm]

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.