Help using Base SAS procedures

excel to dataset

Reply
Frequent Contributor
Posts: 145

excel to dataset

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

Super User
Super User
Posts: 7,401

Re: excel to dataset

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

Frequent Contributor
Posts: 145

Re: excel to dataset

Hi ,

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

Super User
Super User
Posts: 7,401

Re: excel to dataset

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

Frequent Contributor
Posts: 145

Re: excel to dataset

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.

Super User
Super User
Posts: 7,401

Re: excel to dataset

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

Valued Guide
Posts: 2,175

Re: excel to dataset

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

Frequent Contributor
Posts: 145

Re: excel to dataset

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

Super User
Posts: 3,106

Re: excel to dataset

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.

Valued Guide
Posts: 2,175

Re: excel to dataset

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

Valued Guide
Posts: 2,175

Re: excel to dataset

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


Ask a Question
Discussion stats
  • 10 replies
  • 306 views
  • 0 likes
  • 4 in conversation