BookmarkSubscribeRSS Feed
rakeshvvv
Quartz | Level 8

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

rakeshvvv
Quartz | Level 8

Hi ,

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

rakeshvvv
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Peter_C
Rhodochrosite | Level 12

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

rakeshvvv
Quartz | Level 8

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

SASKiwi
PROC Star

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.

Peter_C
Rhodochrosite | Level 12

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

Peter_C
Rhodochrosite | Level 12

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1042 views
  • 0 likes
  • 4 in conversation