Not able to execute Infile statement

[Sourav_sas]

/*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

[SASKiwi]

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.

[Sourav_sas]

Thanks for the help, can you just help me with other options I asked for. below my post.

[SASKiwi]

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.

[New_2_this]

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

[Sourav_sas]

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

[Kurt_Bremser · Accepted solution]

Try

encoding=wlatin1

wlatin1 is typical for Western Europe.

[Sourav_sas]

Finally got..

Thanks Kurt for helping..

Thanks

Regards

Sourav

[Kurt_Bremser]

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.

[Patrick]

@Sourav_sas

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.

[Sourav_sas]

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

[Tom]

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.

[Sourav_sas]

Thanks Tom for your help me complete the import program.

Thanks

Regards

Sourav