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.
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.
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$";
Try adding dbms=excel to your code.
https://communities.sas.com/t5/SAS-Procedures/Importing-a-xlsx-file-into-SAS/td-p/113434
Then take Excel out of it altogether. Unload from the database into a sensible, character-based format, and import that into SAS.
Excel is a format for spreadsheets, not for database data.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.