BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hungling108
Calcite | Level 5

1 PROC IMPORT OUT= WORK.b1
2 DATAFILE= "D:\Pap10501_CGMA\test\dRI1.xlsx"
3 DBMS=EXCEL REPLACE;
4 RANGE="Sheet1$";
5 GETNAMES=NO;
6 MIXED=NO;
7 SCANTEXT=YES;
8 USEDATE=YES;
9 SCANTIME=YES;
10 RUN;

NOTE: create WORK.B1 dataset。
NOTE: dataset WORK.B1 has 3693 observations and 255 variables.

 

 Actually, excel file has 2000 columns.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User
Try using DBMS=XLSX instead of DBMS=EXCEL. That will tell SAS to use a non-microsoft method to read the XLSX file.

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry what does this mean:

4 RANGE="工作表1$";

 

A range options specifies which cell range to use for import, its likely you just nee to update that.  However that being said, you would probably be better off saving the XLSX as CSV, and then writing a datastep import, as you then have much more control over it.  I.e.

data imported_data;
  infile "<your_csv>.csv";
  informat col1 ...;
  format col1 ...;    
  input  col1 $
           col2 
           col3 $
...;
run;

Howevet that being said, why do you have a spreadsheet with 2000 columns?  Nobody is ever going to review that amount of information, assess where the data has come from, and wether you can get the data in a better format than that, i.e. if it came from a database, export from the database a normalised data structure directly to CSV.

hungling108
Calcite | Level 5

The excel file is downloaded from datastream database.

I have to deal with those files into one sas file.

The line 4 RANGE="工作表1$";  ==> RANGE="Sheet1$";

Tom
Super User Tom
Super User
Try using DBMS=XLSX instead of DBMS=EXCEL. That will tell SAS to use a non-microsoft method to read the XLSX file.
hungling108
Calcite | Level 5

Thanka for everyone reply. 

 

The following code is successful for importing Excel (.xlsx) into SAS 9.4.


DBMS=XLSX;

==> The import data set has 70167 observations and 2001 variables.

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 4376 views
  • 3 likes
  • 4 in conversation