03-26-2018 12:58 AM
/*Not able to execute Infile statement */
I am not able to execute one simple code,
what is wrong happening here I don't know...
The code is
Data Work.Test; LENGTH IN_SCOPE $ 11 REPORT_NAME $ 48 FORMATTED_DATA_ELEMENT_NAME $ 100 KIND_OF_ELEMENT $ 140 DATA_ELEMENT_NAME $ 50 DATA_ELEMENT_NAME_DESCRIPTION $ 103 ACCHERID_EC_ID $ 10 KPI $ 1000 SOURCE_DEL $ 69 TABLE_NAME $ 50 COLUMN_NAME $ 50 REMARKS $ 1000 JIRA_NUMBER $ 8 DATE_OF_ENTRY $ 50 NAME $ 8 IN_PROD $ 8 ; LABEL KIND_OF_ELEMENT = "KIND OF ELEMENT" JIRA_NUMBER = "JIRA NUMBER" IN_PROD = "IN PROD" ; Infile "/var/opt/data/data_project/sas94/bire/redwh/data/tmp/Book2.xlsx" Missover DSD ; INPUT IN_SCOPE : $CHAR11. REPORT_NAME : $CHAR48. FORMATTED_DATA_ELEMENT_NAME : $CHAR100. KIND_OF_ELEMENT : $CHAR140. DATA_ELEMENT_NAME : $CHAR50. DATA_ELEMENT_NAME_DESCRIPTION : $CHAR103. ACCHERID_EC_ID : $CHAR10. KPI : $CHAR1000. SOURCE_DEL : $CHAR69. TABLE_NAME : $CHAR50. COLUMN_NAME : $CHAR50. REMARKS : $CHAR1000. JIRA_NUMBER : $CHAR8. DATE_OF_ENTRY : $CHAR50. NAME : $CHAR8. IN_PROD : $CHAR8. ; Run;
After executing this code I am getting output like this
Why it is happening I really don't know, previously I have executed same code, got outputs, now it is not working, I am using SAS EG 7.13.
Please suggest how to import .xlsb using the same code. Please mention one option to specify the sheet name, I tried with sheetname option but that is not working.
I am attaching the sample dataset also for your help.
03-26-2018 02:20 PM
03-26-2018 01:03 AM - edited 03-26-2018 01:04 AM
You can't import a spreadsheet via a DATA step like it was a text file. Best way is to FILE SAVE AS to a CSV file in Excel then your code will most likely work.
03-26-2018 03:13 AM
Any Excel-type workbook cannot be read the way you are trying - XLS, XLSB, XLSX. The INFILE statement requires a text file as input with data in fixed or delimited columns. You need to convert your Excel file using FILE SAVE AS in Excel to a delimited-column file to use the code you have.
03-26-2018 05:00 AM
Thank you for your question.
i will attempt to assist you.
You wish to import an excel file.
proc import out = Output;
Datafile= '\\PlacePath Where File can be found\.xls';
It is best to convert your excel file to csv. Do this by using the Save As option in excel.
Excel tends to tamper with the data.
Hope this helps.
03-26-2018 01:40 PM
I came to know about a different option is SAS, But I have tried with
INFILE "/var/opt/data/data_project/sas94/bire/redwh/data/tmp/Record.csv" MISSOVER TRUNCOVER DSD ENCODING = "utf-8" FIRSTOBS = 2 ;
Still the output is coming as the previous one. I am using SAS EG 7.13
and the format I have in that column as germen.
Can you help me with some different option here.
03-26-2018 02:20 PM
03-26-2018 04:50 AM
This code is most probably code you have taken from a Enterprise Guide Import task.
Enterprise Guide converts the Excel file locally to a standardized text format, sends that text to the server, and executes the data step there.
Without the EG conversion, you can't read the Excel file in a data step.
One workaround is to achieve the conversion to text by saving to csv from Excel, but be aware that the resulting file will look diferently from what EG created, so you will have to adapt the data step code to that.
Another workaround is to use proc import with dbms=xlsx, which needs a SAS ACCESS to PC Files license; with that license, you can also use libname xlsx to treat the Excel workbook like a SAS library. Both of those methods are not suitable for any kind of production use, as they force SAS to make guesses about the contents, which will lead to structurally different datasets with each execution for a new input.
Bottom line: use a textual format for data transfer between applications, it is the only way where you keep control of the process.
03-26-2018 05:31 AM
Just adding to what @KurtBremser writes:
1. If this is an ad-hoc task and the data in your Excel changes then just keep things as they were when you've used the import wizard. Leave this wizard in the code and if the Excel changes just drop it onto the same place in your local environment and execute the EG task again.
2. If you just use EG to generate the code but the Excel will later on reside on the SAS Server side: That's not going to work! As explained SAS EG does locally the conversion for you (client side) and then sends a text file to the SAS Server. The generated SAS data step is for this converted text file. ...and you're getting this "weird" path in the code as this is the path pointing to this temporarily created text file.
Excel files are not simple text files. If you want to get some insights what .xlsx and .xlsb really are: Use a tool like WinZip and try to open the .xlsx. That will show you that this is actually an archive with lots of XML files in it.
.XLSB is different. The ...b stands for "binary" so here you need Microsoft Excel components to access anything.
03-26-2018 12:40 PM
Thanks Patrick and Kurt and others also,
Right now this following code is working
DATA Work.META_FINREP_REG_5_FORMSCOLUMN; LENGTH REPORT_KEY $ 50 REPORT_VARIANT $ 50 COLUMN_TEXT $ 500 PAGE $ 2 ROW1 $ 4 COLUMN1 $ 4 VALID_FROM 8 VALID_TO 8 LOAD_TIMESTAMP 8 ; FORMAT REPORT_KEY $CHAR50. REPORT_VARIANT $CHAR50. COLUMN_TEXT $CHAR500. PAGE $CHAR2. ROW1 $CHAR4. COLUMN1 $CHAR4. VALID_FROM DATETIME20. VALID_TO DATETIME20. LOAD_TIMESTAMP DATETIME20. ; INFORMAT REPORT_KEY $CHAR50. REPORT_VARIANT $CHAR50. COLUMN_TEXT $CHAR500. PAGE $CHAR2. ROW1 $CHAR4. COLUMN1 $CHAR4. VALID_FROM DATETIME20. VALID_TO DATETIME20. LOAD_TIMESTAMP DATETIME20. ; INFILE "/var/opt/data/data_project/sas94/bire/redwh/data/tmp/Record.csv" MISSOVER TRUNCOVER DSD FIRSTOBS = 2 ; INPUT REPORT_KEY : $CHAR50. REPORT_VARIANT : $CHAR50. COLUMN_TEXT : $CHAR500. PAGE : $CHAR2. ROW1 : $CHAR4. COLUMN1 : $CHAR4. VALID_FROM : DATETIME20. VALID_TO : DATETIME20. LOAD_TIMESTAMP : DATETIME20. ; RUN;
But I have another challenge
In my raw dataset
one column is there named "COLUMN_TEXT". In this column I have some data like "übernommene VG". This one is germen format.
When I am importing this one, in SAS output it is coming as
How can I solve this one. I am trying with different formats but it is not working.
Can you help me on this.
03-26-2018 01:21 PM
If the text is not appearing correctly then you have an issue with the ENCODING. Most likely you are running SAS with a single byte encoding and the data file is use multi-byte UTF-8 encoding. In that case you need to start sas using UTF-8 encoding.
There is a small possibility that the source file is using a different single byte encoding than your SAS session. In that case you can try the ENCODING option on the INFILE statement to tell it what encoding to use when reading the file.
Need further help from the community? Please ask a new question.