/*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.
More points,
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.
Thanks
Regards
Sourav
Try
encoding=wlatin1
wlatin1 is typical for Western Europe.
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.
Thanks for the help, can you just help me with other options I asked for. below my post.
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.
Hi Sourav_sas,
Thank you for your question.
i will attempt to assist you.
My Understanding:
You wish to import an excel file.
Suggested Solution:
Title "PutAtitleHere";
proc import out = Output;
Datafile= '\\PlacePath Where File can be found\.xls';
DBMS=Excel2010 Replace;
Sheet='Sheet1';
GETNAMES=YES;
MIXED=NO;
run;
Notes
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.
Regards,
New_2_this
Thanks SASKiwi
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
;
This option
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.
Thanks
Regards
Sourav
Try
encoding=wlatin1
wlatin1 is typical for Western Europe.
Finally got..
Thanks Kurt for helping..
Thanks
Regards
Sourav
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.
Just adding to what @Kurt_Bremser 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.
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.
Thanks
Regards
Sourav
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.
Thanks Tom for your help me complete the import program.
Thanks
Regards
Sourav
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: