BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
anzabob
Fluorite | Level 6

I am using proc import to get xlsx file to SAS like this:

 

PROC IMPORT
OUT=work.W32HONJM

DATAFILE="filename.xlsx"
DBMS=XLSX Replace;
GETNAMES=YES;

RUN;

 

The file in question only has one sheet, the first row has the column names and then there is a mix of number and text data for the columns.

 

In the output table i get all the data rows (33), but no columns. (total rows 34).  Is there something wrong in the code, or can someone please suggest an alternative solution.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

What you need in DIS are stable table structures. Something you won't necessarily get with a Proc Import.

A purist DIS way would be to define a libname in SAS Metadata pointing to this Excel file, register the Excel sheet as table and then use this table metadata in your DIS job. I've done that in the past and it works - documentation here.

Another approach discussed here

View solution in original post

6 REPLIES 6
AllanBowe
Barite | Level 11

As you asked for an alternative solution (and please note - this totally biased, because I created the tool), you could consider Data Controller for SAS

 

This empowers your business user to load the file directly into the Target table. No need for a DI job, nothing to promote, full history of changes made, can load any database, and a copy of the excel is kept with every upload.

 

Here's a video to explain more:

 

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
anzabob
Fluorite | Level 6
Unfortunately, solutions outside DI Studio are not an option. I should have been more specific.
Patrick
Opal | Level 21

What you need in DIS are stable table structures. Something you won't necessarily get with a Proc Import.

A purist DIS way would be to define a libname in SAS Metadata pointing to this Excel file, register the Excel sheet as table and then use this table metadata in your DIS job. I've done that in the past and it works - documentation here.

Another approach discussed here

anzabob
Fluorite | Level 6
That is the solution i was looking for, much appreciated! 🙂
RacheLGomez123
Fluorite | Level 6

One of the most common CSV import errors is that the file is simply too large, and the same holds true for Excel files. It may be caused by too many fields or records in the file, too many columns, or too many rows. This Excel import error can be caused by limits set by the program using the file or the amount of available memory on the system. If your import fails because of file size issues, you need to go back and break the file up into smaller files, which will enable it to avoid this file error and successfully upload.

 

Regards,

Rachel Gomez

Patrick
Opal | Level 21

@RacheLGomez123 Besides of you replying to an old track that talks about Excel: Is your comment "generic" or based on real experience with SAS reading .csv files? Proc Import generates data step code for reading a .csv and I'm not aware of any size limitations that would be a problem for real live scenarios.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 2414 views
  • 1 like
  • 4 in conversation