topic Re: Import xlsx - weird formatting on mixed columns in SAS Programming

Import xlsx - weird formatting on mixed columns

lbarwick — Thu, 10 Jun 2021 14:57:05 GMT

Routinely I export data from my company's Jira service desk. I have to export it as a .csv then save it as a .xlsx, otherwise SAS seems to truncate the number of variables that get imported. Anyway, one of the columns contains free text data like this:

Ticket #	FTE allocation
001	100
002	100%
003	1
004	75-100
005	50%
006	50 to 100%

This is how the data appear in Jira and in .csv and the .xlsx prior to import. I'm using a basic import procedure:

proc import out=prr 
		datafile = "C:\Users\lbarwick\Desktop\Jira_Data.xlsx"
		dbms=xlsx replace;
	getnames=Yes; 
run;

What gets processed/imported is this:

Ticket #	FTE allocation
001	100
002	1
003	1
004	75-100
005	0.5
006	50 to 100%

You can see that whenever the value contains %, the value is changed to a fraction of a whole number, otherwise SAS seemingly is retaining the native value as text. I know mixed option is implicit, and I've tried sorting the .xlsx differently prior to import so that explicit character data are in the first few rows to see if SAS accurately interprets all rows as character data. Nothing seems to be working! Any thoughts? I've asked my IT dept about changing registry data to update typeguessrows to 0 but it can't be done.

Re: Import xlsx - weird formatting on mixed columns

Reeza — Thu, 10 Jun 2021 15:07:16 GMT

Read it in from the CSV not XLSX and fix it there. You have no control over types/formats with Excel but you do with CSV. For truncation, you likely need to specify LRECL to specify the record length if you have a really wide data set.

Re: Import xlsx - weird formatting on mixed columns

jimbarbour — Thu, 10 Jun 2021 15:11:39 GMT

Check your SAS log. Proc import should be generating SAS code for a DATA step behind the scenes. What you might do is to copy that DATA step code and modify it so that the importation goes according to your rules rather than the default. However, 100% is probably represented in Excel as a number with a format, that number being 1 in the case of 100%. You may have to play with it. Importing it from .csv is probably going to give you a lot more control than importing it from Excel. Your INFILE statement should probably include DSD DLM=',' for csv.

Jim

Re: Import xlsx - weird formatting on mixed columns

Tom — Thu, 10 Jun 2021 15:28:14 GMT

When you have mixed cells in a single column the variable is created as CHAR. For any cell that has a numeric value instead of a string the RAW value is translated to a text string. So any display format the cell might have attached to in the spreadsheet is lost. SAS does the same thing with date values.

Have the creator of the spreadsheet make sure to define all of the cells in the column as strings instead of numbers and you will get the percent signs.

Re: Import xlsx - weird formatting on mixed columns

lbarwick — Thu, 10 Jun 2021 17:48:57 GMT

Understood but the 'creator' of the spreadsheet in this case is Jira, I get the .csv as an export and then save it as .xlsx. I tried converting all of the values in this column to text and Excel will turn 100% into 1 and 50% into 0.5. The problem with this is that there could be other rows with a legitimate value of 1 (1%) so in this conversion I cannot distinguish between what was originally 100% and what was originally 1%.

Re: Import xlsx - weird formatting on mixed columns

lbarwick — Thu, 10 Jun 2021 17:50:29 GMT

I've tried reading it in as .csv and SAS does not like this. The file that I get out of Jira is very messy when opened as a .csv in text editor, but for some reason is structured just fine when I save the .csv as a .xlsx.

Re: Import xlsx - weird formatting on mixed columns

Reeza — Thu, 10 Jun 2021 17:54:35 GMT

What does "SAS does not like this" mean?
Did it the error message say "SAS doesn't like your data"?

I suspect it means your data didn't import correctly and there's various reasons this can happen but most are easily fixed. Converting your file to XLSX will cause your more issues in the long run - like you're experiencing. You can choose which problem to fix, but in my opinion it makes more sense to fix your data import from CSV than to try and decode anything after the fact.

Re: Import xlsx - weird formatting on mixed columns

lbarwick — Thu, 10 Jun 2021 18:04:24 GMT

The import happens just fine with .csv - the issue is with how the data are structured. Example below - .csv from Jira contains many hard returns:

The result from importing into SAS is something like this where you can see values shifted and in the wrong columns (see second and last rows - that value should not be in that column):

I'm trying to avoid as much as possible having to manually manipulate files before importing into SAS.

Re: Import xlsx - weird formatting on mixed columns

jimbarbour — Thu, 10 Jun 2021 18:20:45 GMT

Well, pick your "poison." You can

Save the .csv as an Excel file, but Excel is going to automatically butcher things like numbers with a percent sign. You may be able to do something with Excel to handle the columns as text, but you'll have to play with it.
Use the .csv file directly, but you'll have to do something like using the TRANSLATE function to clean out special characters. The good news here is that you can use SAS code to clean the data; you don't have to do this manually.

So, pick your "poison." I don't think there's a way you can do it that doesn't involve some "fiddling" in conjunction with importing the data into SAS.

Jim

Re: Import xlsx - weird formatting on mixed columns

lbarwick — Thu, 10 Jun 2021 18:21:02 GMT

The .csv formatting issue is due to records exported from Jira that contain hard returns (e.g. a freetext field where user added a hard return) that essentially corrupts the exported file.

Re: Import xlsx - weird formatting on mixed columns

lbarwick — Thu, 10 Jun 2021 18:22:51 GMT

I'm sort of coming to the same conclusion, my issue is I would need to manually manipulate my csv before importing it into SAS. In the excel, changing that column to text has the same exact effect I'm experiencing when importing the excel into SAS.

Re: Import xlsx - weird formatting on mixed columns

jimbarbour — Thu, 10 Jun 2021 18:27:10 GMT

Do you know the specific Hexidecimal value? If so, you can use a Data step to cleanse these. Presumably, a "hard return" is '0A'x or '0D'x (probably '0A'x). You should be able to use TRANSLATE or COMPRESS to clean these out. You'd probably have to read the data as a Byte stream and apply COMPRESS(VARNAME, , 'kw')' which should delete any non display characters.

Jim

Re: Import xlsx - weird formatting on mixed columns

Tom — Thu, 10 Jun 2021 18:30:22 GMT

It is generally not hard to fix a delimited text file that has embedded end of line characters so that SAS can read it.

You might not even need to change the text file at all. If JIRA has generated the lines with CR+LF at the end and used only LF or only CR when inserting line breaks into the middle of the values of some of the fields. Or if the lines always end with LF and the embedded line break character is always a CR (or the reverse). Just use the TERMSTR= option on the INFILE or FILENAME statement.

Otherwise if the text file is using proper quoting (or even proper escaping) of the values with embedded line breaks then it is not hard to convert the file into one that SAS can read. For example by replacing all of the embedded CR and/or LF characters with some other character. If Excel can read the files then you should be able to write a SAS program to read it and fix it.

If you do want to read the file into EXCEL and then save it back out just force EXCEL to read each field as CHARACTER instead of letting it guess which cells should be considered numbers. So instead of double-clicking the CSV file to open it in Excel instead you can first start Excel and then using the data import (or where ever they have moved that option in the current release). That way you can tell it to treat every column as TEXT.

Re: Import xlsx - weird formatting on mixed columns

Tom — Thu, 10 Jun 2021 18:33:55 GMT

Please go support this now 3+ year old SASWARE Ballot Idea for SAS to make enhancement to allow easy processing of such text files.

https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhancements-to-INFILE-FILE-to-handle-delimited-file-variations/idi-p/435977

Re: Import xlsx - weird formatting on mixed columns

Tom — Thu, 10 Jun 2021 18:58:48 GMT

@lbarwick wrote:
I'm sort of coming to the same conclusion, my issue is I would need to manually manipulate my csv before importing it into SAS. In the excel, changing that column to text has the same exact effect I'm experiencing when importing the excel into SAS.

Don't try to fix it after EXCEL has tried to convert the text that is in the file into numbers/dates/percents. Just make sure you tell EXCEL to read everything as TEXT to begin with.

Re: Import xlsx - weird formatting on mixed columns

ballardw — Thu, 10 Jun 2021 19:02:43 GMT

@lbarwick wrote:
Understood but the 'creator' of the spreadsheet in this case is Jira, I get the .csv as an export and then save it as .xlsx. I tried converting all of the values in this column to text and Excel will turn 100% into 1 and 50% into 0.5. The problem with this is that there could be other rows with a legitimate value of 1 (1%) so in this conversion I cannot distinguish between what was originally 100% and what was originally 1%.

So EXCEL is the culprit. This is a well know issue. Excel makes a lot of assumptions and converts stuff without giving you much option without going through a lot of work. Just yesterday I had an age range exported from the US census website that should be 1-21 that opening the exported file became a DATE of Jan 2021 because of EXCEL.

Read the CSV into SAS instead of that conversion step.

If the file has the same structure then you can set all the variable properties as you want and only change in the input file name and output data set for each new file.

Plus code to read CSV can be modified to read a bunch of files at one time.

Re: Import xlsx - weird formatting on mixed columns

ErikLund_Jensen — Thu, 10 Jun 2021 19:31:18 GMT

Hi @lbarwick

I have encountered the same problem several times, and I have found the following approach useful. There is no conversion outside SAS.

1. Ged rid of the embedded line shifts. Use a data step to read the CSV file as text lines. Store the line in a retained output-line variable. Count the number of delimiters in the current line and add to the output line until it has the expected number of deliniters. Then write the output-line variable to a temporary text file, initiate the output-line variable and counter and read next line etc.

2.Use another data step to read the temporary file into SAS as a delimited file. I prefer to read all variables as text and convert them to numeric or dates or whatever where appropriate.

Re: Import xlsx - weird formatting on mixed columns

lbarwick — Thu, 10 Jun 2021 20:10:11 GMT

I don't suppose you have example code for this solution?

Re: Import xlsx - weird formatting on mixed columns

Tom — Thu, 10 Jun 2021 20:33:05 GMT

@lbarwick wrote:
I don't suppose you have example code for this solution?

There are literally dozens if not hundreds of examples on this site alone.

Try a simple google search:

https://www.google.com/search?q=%40sas.com+embedded+line+breaks+in+csv+file

Here are a couple of solutions:

https://communities.sas.com/t5/New-SAS-User/Reading-CSV-file-with-double-quotes-line-breaks-and-spaces/td-p/505738

https://communities.sas.com/t5/SAS-Programming/How-to-import-line-feed-or-carriage-return-values-from-csv-file/m-p/656445/highlight/true#M196822

SAS has even posted a note about this, but watch out as that method modifies the original file.

https://support.sas.com/kb/26/065.html

Re: Import xlsx - weird formatting on mixed columns

andreas_lds — Fri, 11 Jun 2021 04:27:56 GMT

@lbarwick wrote:
Understood but the 'creator' of the spreadsheet in this case is Jira, I get the .csv as an export and then save it as .xlsx. I tried converting all of the values in this column to text and Excel will turn 100% into 1 and 50% into 0.5. The problem with this is that there could be other rows with a legitimate value of 1 (1%) so in this conversion I cannot distinguish between what was originally 100% and what was originally 1%.

So, don't open the csv-file with excel, write a data step to read it directly. The code depends on the contents of the file, so it is hardly possible to suggest something useful.