import mutiple sheets XML spreadsheet and Excel file

[RajasekharReddy]

Dear user,

i am trying to import multiple sheets XML spread sheet and excle file in to data sets (one sheet for one data set) by using below methods

-proc imoprt

-ods tagsets

-libname

how ever i am unable to create data sets, can any one help me to resolve this issue

Regars,

Rajasekhar Reddy

[kannand]

Here is one way to get the tabs imported. In this example, I've used a xls with 3 tabs named TAB1,TAB2 and TAB3. 

 The Excel sheet is attached below:

%macro xlfile(file);
proc import out= &file
    datafile = '/folders/myfolders/data/SASTABSv5.xls'
    dbms = xls;
    sheet = "&file";
    getnames = yes;
run;
%mend xlfile;
%xlfile(TAB1);
%xlfile(TAB2);
%xlfile(TAB3);

proc contents data=work._all_;

Here is the log for your reference:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 55         
 56         %macro xlfile(file);
 57         proc import out= &file
 58             datafile = '/folders/myfolders/data/SASTABSv5.xls'
 59             dbms = xls;
 60             sheet = "&file";
 61             getnames = yes;
 62         run;
 63         %mend xlfile;
 64         %xlfile(TAB1);
 
 NOTE: The import data set has 2 observations and 2 variables.
 NOTE: WORK.TAB1 data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.01 seconds
       cpu time            0.01 seconds
       
 
 65         %xlfile(TAB2);
 
 NOTE: The import data set has 2 observations and 2 variables.
 NOTE: WORK.TAB2 data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 66         %xlfile(TAB3);
 
 NOTE: The import data set has 2 observations and 2 variables.
 NOTE: WORK.TAB3 data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 67         
 68         proc contents data=work._all_;
 69         
 70         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 82         

And, here is the output...

The CONTENTS Procedure
Directory
Libref	WORK
Engine	V9
Physical Name	/tmp/SAS_workA7D900000D63_localhost.localdomain/SAS_workBA9100000D63_localhost.localdomain
Filename	/tmp/SAS_workA7D900000D63_localhost.localdomain/SAS_workBA9100000D63_localhost.localdomain
Inode Number	275783
Access Permission	rwx------
Owner Name	sasdemo
File Size	4KB
File Size (bytes)	4096
#	Name	Member Type	File Size	Last Modified
1	REGSTRY	ITEMSTOR	32KB	11/24/2015 05:29:06
2	SASGOPT	CATALOG	12KB	11/24/2015 06:31:55
3	SASMAC1	CATALOG	188KB	11/24/2015 05:29:07
4	TAB1	DATA	128KB	11/24/2015 06:42:37
5	TAB2	DATA	128KB	11/24/2015 06:42:37
6	TAB3	DATA	128KB	11/24/2015 06:42:37
The CONTENTS Procedure
Data Set Name	WORK.TAB1	Observations	2
Member Type	DATA	Variables	2
Engine	V9	Indexes	0
Created	11/24/2015 01:42:37	Observation Length	24
Last Modified	11/24/2015 01:42:37	Deleted Observations	0
Protection	 	Compressed	NO
Data Set Type	 	Sorted	NO
Label	 	 	 
Data Representation	SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64	 	 
Encoding	utf-8 Unicode (UTF-8)	 	 
Engine/Host Dependent Information
Data Set Page Size	65536
Number of Data Set Pages	1
First Data Page	1
Max Obs per Page	2714
Obs in First Data Page	2
Number of Data Set Repairs	0
Filename	/tmp/SAS_workA7D900000D63_localhost.localdomain/SAS_workBA9100000D63_localhost.localdomain/tab1.sas7bdat
Release Created	9.0401M3
Host Created	Linux
Inode Number	275959
Access Permission	rw-rw-r--
Owner Name	sasdemo
File Size	128KB
File Size (bytes)	131072
Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
1	A	Char	12	$12.	$12.	A
2	B	Num	8	BEST12.	 	B
The CONTENTS Procedure
Data Set Name	WORK.TAB2	Observations	2
Member Type	DATA	Variables	2
Engine	V9	Indexes	0
Created	11/24/2015 01:42:37	Observation Length	24
Last Modified	11/24/2015 01:42:37	Deleted Observations	0
Protection	 	Compressed	NO
Data Set Type	 	Sorted	NO
Label	 	 	 
Data Representation	SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64	 	 
Encoding	utf-8 Unicode (UTF-8)	 	 
Engine/Host Dependent Information
Data Set Page Size	65536
Number of Data Set Pages	1
First Data Page	1
Max Obs per Page	2714
Obs in First Data Page	2
Number of Data Set Repairs	0
Filename	/tmp/SAS_workA7D900000D63_localhost.localdomain/SAS_workBA9100000D63_localhost.localdomain/tab2.sas7bdat
Release Created	9.0401M3
Host Created	Linux
Inode Number	275960
Access Permission	rw-rw-r--
Owner Name	sasdemo
File Size	128KB
File Size (bytes)	131072
Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
2	B	Num	8	BEST12.	 	B
1	D	Char	12	$12.	$12.	D
The CONTENTS Procedure
Data Set Name	WORK.TAB3	Observations	2
Member Type	DATA	Variables	2
Engine	V9	Indexes	0
Created	11/24/2015 01:42:37	Observation Length	24
Last Modified	11/24/2015 01:42:37	Deleted Observations	0
Protection	 	Compressed	NO
Data Set Type	 	Sorted	NO
Label	 	 	 
Data Representation	SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64	 	 
Encoding	utf-8 Unicode (UTF-8)	 	 
Engine/Host Dependent Information
Data Set Page Size	65536
Number of Data Set Pages	1
First Data Page	1
Max Obs per Page	2714
Obs in First Data Page	2
Number of Data Set Repairs	0
Filename	/tmp/SAS_workA7D900000D63_localhost.localdomain/SAS_workBA9100000D63_localhost.localdomain/tab3.sas7bdat
Release Created	9.0401M3
Host Created	Linux
Inode Number	275961
Access Permission	rw-rw-r--
Owner Name	sasdemo
File Size	128KB
File Size (bytes)	131072
Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
2	B	Num	8	BEST12.	 	B
1	G	Char	12	$12.	$12.	G

Hope this helps you.... Good Luck...!!!

[RW9]

Please clarify what you mean by "how ever i am unable to create data sets,".  What is the problem, is there something in the log?  Are you unsure of the code?  What have you tried?  What does the file look like, is it an Excel file (i.e. is it XLSX), sample would be helpful.

[Reeza]

ODS Tagsets is used for exporting data, not importing data.

Proc Import and Libname methods may work, assuming you have the correct license, in this case SAS/Access.