Issue while Excel (xlsx) file into SAS

[Vemula]

Hi,

Greetings!

I am facing issue while importing xlsx file into SAS. The character values in a column are converted to numeric values in the SAS dataset. I have checked the column in the input file if it is having any formats applied. But there are none. The character value "TRUE" is converted to "1" in the sas dataset. I have attached the screenshots of Code, Input file and Output SAS dataset.

I tried converting the file into .xls format and import it. But I am facing the same issue. 

I am using SAS E.G 7.1 on Windows.

Could you please help me to sort out this issue? 

Thanks much in advance for your support!

Regards,

Vemula

[Reeza]

Which column is it?

[Vemula]

Hi Reeza,

Thanks for responding.

The column name is Contents. 

Regards,

Vemula

[Reeza]

Yeah, we do need a copy of the Excel file, but check the type of the cell values using: 

isnumber() or islogical() in Excel. If it's logical, I suspect it's being read in as 1/0. 

But, you also have character values in that column from your screenshot, so that means it has to be a character column. A column cannot have multiple types of data. You could create a custom informat to ignore the NA values if that's your issue, but it would still be an after import conversion. If you want the most control over the file when importing it, convert it to CSV and then write a data step to import the data.

---

@Vemula wrote:

Hi Reeza,

Thanks for responding.

The column name is Contents. 

Regards,

Vemula

---

[Vemula]

Hi Reeza,

Thanks much for your inputs.

I checked islogical(), isnumber() and istext() functions in Excel. I got Isnumber() as "False" and Istext() as "False". I got islogical() as "True". The file cannot be converted to csv as there are 31 tabs in the input file which need to be compared with the other input file. Is there any other way to resolve this issue. 

Thanks again for your advice.

Regards,

Vemula

[Reeza]

---

@Vemula wrote:

Hi Reeza,

Thanks much for your inputs.

I checked islogical(), isnumber() and istext() functions in Excel. I got Isnumber() as "False" and Istext() as "False". I got islogical() as "True". The file cannot be converted to csv as there are 31 tabs in the input file which need to be compared with the other input file. Is there any other way to resolve this issue. 

Thanks again for your advice.

Regards,

Vemula

---

If ISLOGICAL is true, then that field shows as TRUE, but Excel's underlying value is really a 0/1 which SAS is reading. 

If it was text it would read in as TRUE. I think recoding the data after you read it in for those columns is your best bet. 

[Vemula]

Hi Reeza,

Thanks much for your inputs.

It really helped to understand the issue and figure out how to deal with it. Appreciate your support.

Regards,

Vemula

[ballardw · Accepted solution]

---

@Vemula wrote:

Hi,

Greetings!

I am facing issue while importing xlsx file into SAS. The character values in a column are converted to numeric values in the SAS dataset. I have checked the column in the input file if it is having any formats applied. But there are none. The character value "TRUE" is converted to "1" in the sas dataset. I have attached the screenshots of Code, Input file and Output SAS dataset.

I tried converting the file into .xls format and import it. But I am facing the same issue. 

I am using SAS E.G 7.1 on Windows.

Could you please help me to sort out this issue? 

Thanks much in advance for your support!

Regards,

Vemula

---

Open an Excel File. Type the word True into cell without a leading ' . Notice that the value jumps to the center of the cell? Or if you type in lower case it becomes capitalized? It has been converted to a Boolean value which the import treats as 1/0 in SAS. Type 'True in another cell. Note that it does not center, it is an actual Text value. Just because it appears as text in Excel does not mean that it is.

Another example: In Excel type the formula: =(1=1). Notice that when you hit enter you now see TRUE.

And this behavior makes a lot of sense. A variable with values of 1/0 can be used in comparisons as:

If var then do...

when var=1 the do part executes and when =0 it doesn't.

Summary statistics also work better for many tables: N= number of responses, Sum=number of true/yes or what have you responses, mean=percent of true and in report procedures like Tabulate and Report you can request PCTSUM and some variations that would be very obnoxious to count and calculate using an actual Text value.

If you want to see the value of True/False in your data sets use a custom format like:

proc format library=work;
value tf
1='True'
0='False'
;
run;

and use the TF. format as desired.

[Vemula]

Hi Ballard,

Thanks much for the detailed explanation on the steps to identify the boolean and text values, pros of the boolean values and the formatting procedure. 

I will format these values and use it in comparison. Appreciate your support.

Regards,

Vemula