topic Re: excel to dataset in SAS Procedures

excel to dataset

rakeshvvv — Wed, 09 Apr 2014 12:21:21 GMT

I have a 2010 excel with N number of tab sheets..i would like to create a program which

would create N number of datasets with tab sheets name as name of the dataset....if tab sheets

have numerical number as name....sas should assign default name to the dataset otherwise name of the

tabsheet as dataset name....

Thanks

Re: excel to dataset

RW9 — Wed, 09 Apr 2014 12:29:15 GMT

Hi,

Use:

libname my_xls excel "S:\Temp\Rob\tmp.xlsx" mixed=yes;

This will show you all the worksheets within your XLS file, it should be a simple matter to loop over them and create datasets. E.g.

data sheet1;

set my_xls.'sheet1$';

run;

Reference: http://www2.sas.com/proceedings/sugi31/024-31.pdf

Re: excel to dataset

Peter_C — Wed, 09 Apr 2014 13:23:19 GMT

whatever worksheet tab names are present, SAS can use them using the style I refer to as "name constant.

I.E. 'tabname'n

So If the tabs are just numbers 1,2,3,4 then a data step could bring them all in with code like

libname yours 'your excel workbook path\name' mixed= yes access= readonly ;

data all_together ;

set yours.'1'n yours.'2.'n yours.'3'n yours.'4'n ;

run ;

of course results depend on the qualitty of the data in excel .

peterC

Re: excel to dataset

rakeshvvv — Wed, 09 Apr 2014 13:42:03 GMT

Hi ,

I am not able to assign library name with above format.......it says invalid format

Re: excel to dataset

RW9 — Wed, 09 Apr 2014 13:55:04 GMT

Missing the engine statement - excel, please see my previous post.

Re: excel to dataset

rakeshvvv — Wed, 09 Apr 2014 14:05:21 GMT

I Used your previous code but i was getting following error.... I am using 9.2 version of sas

libname my_xls excel "/xxxxxxxxxxxxxx/abc.xlsx" mixed=yes;

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

Re: excel to dataset

RW9 — Wed, 09 Apr 2014 14:11:35 GMT

Strange, do you have SAS/ACCESS component?

It should be available in SAS 9.2 if you have SAS/ACCESS: SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition

Re: excel to dataset

Peter_C — Wed, 09 Apr 2014 14:15:06 GMT

Well, it would work if you licence sas access to pc files

but since you don't, it doesn't.

One workaround involves saving all your data sheets into csv files and loading those

Another involves DDE but in more recent machine systems that has become less reliable for more than small volumes.

Good luck

Re: excel to dataset

Peter_C — Wed, 09 Apr 2014 14:34:47 GMT

Perhaps it is the platform

@rakeshvv

are you running sas on a windows platform, or elsewhere?

Are you running this code in SAS Enterprise Guide connected to a server that is not running on windows?

Then there might be a problem

Re: excel to dataset

rakeshvvv — Wed, 09 Apr 2014 18:07:17 GMT

I am not running it on SAS Enterprise guide but was running on sas unix platform

Re: excel to dataset

SASKiwi — Wed, 09 Apr 2014 19:34:52 GMT

Check your SAS licence - prod setinit noalias; run; If you dont have SAS/ACCESS Interface to PC Files listed then this will explain your errors.