topic Re: Proc import reading numeric values as char in SAS Programming

Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 20:31:48 GMT

Hi there,

I have a dataset and few columns have numeric values but when I reading that excel using the proc import SAS is reading them as character. Is there a way if I can retain the type while proc importing?

for eg : sd_id has values as 3,4,5,6,7,8 similarly I have other few columns causing the same issue but sas is reading it as char. so currently we can see the specific columns showing this behavior but when it goes to production environment we are not even sure if other numeric columns are start getting read as char. Is there a way we can handle this issue.

Regards

Kajal

Re: Proc import reading numeric values as char

ballardw — Thu, 16 Jun 2022 20:50:26 GMT

@kajal_30 wrote:

Hi there,

I have a dataset and few columns have numeric values but when I reading that excel using the proc import SAS is reading them as character. Is there a way if I can retain the type while proc importing?

for eg : sd_id has values as 3,4,5,6,7,8 similarly I have other few columns causing the same issue but sas is reading it as char. so currently we can see the specific columns showing this behavior but when it goes to production environment we are not even sure if other numeric columns are start getting read as char. Is there a way we can handle this issue.

Regards

Kajal

Since EXCEL does not actually have the concept of a variable there is no TYPE involved at all.

Proc Import expects simple data layouts such as at maximum one row of column headings that might be treated as variable names. I see way too many Excel workbooks with multiple rows of header information. So the second (and or 3rd/4th/etc rows) are not actually numeric and the column gets treated as character because there are character values.

In a production environment you really don't want to use import. Not just because of this issue but variable lengths can change because SAS makes the decision for such separately for each file read. If you data source is sloppy you may also have issues with variable names changing as well as type.

The most reliable way to get specific properties for "columns" is to 1) save the file to a text format such as CSV and 2) use a data step to read that resulting file with a data step. One of the options in the INFILE used for reading with a data step allows you to specify which is the first line of actual data.

Re: Proc import reading numeric values as char

Kurt_Bremser — Thu, 16 Jun 2022 20:51:46 GMT

"Production" and "Excel" are mutually exclusive. Excel files are simply useless as a medium for data transfer.

Save your data as text files (e.g. csv), which you can read with a custom written data step, so you have full control and get consistent results.

Re: Proc import reading numeric values as char

Tom — Thu, 16 Jun 2022 20:58:47 GMT

There are two situations that can happen.

1) There is some non numeric cell in that column.

2) All of the cells are empty. In that case PROC IMPORT will create the variable as character of length 1 since that will take 7 bytes less storage in the SAS dataset than a numeric variable.

In either case you have choices.

1) Fix the Excel file so the column only contains numeric values and at least one of them is not empty.

2) Save the data from the Excel into a different format. Either one that enforces variable types, like a DBF file. Or a text file that you can read with a data step.

3) Post process the SAS dataset to try to convert the character values to a numeric variable.

Re: Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 21:01:43 GMT

We will have no control over the excel file as it will be directly be provided by business to the process. so no manual intervention will be allowed.

Re: Proc import reading numeric values as char

Reeza — Thu, 16 Jun 2022 21:02:01 GMT

I'd just add on if this is a 'production' level job I would add a check to ensure that all column types in the data set were correct, not just that one field. Can save you hours of debugging if the job fails at loading the data because types have been modified for some reason. PROC IMPORT doesn't guarantee that types are the same between files especially for Excel files.

Re: Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 21:05:15 GMT

Hi Reeza,

So it can be different each time we are reading the excel file? is it better for me to convert this file into csv format and then read that file using data step and assign lengths and formats as per the final target dataset to be appended to?
Thanks
Kajal

Re: Proc import reading numeric values as char

Kurt_Bremser — Thu, 16 Jun 2022 21:06:10 GMT

@kajal_30 wrote:

We will have no control over the excel file as it will be directly be provided by business to the process. so no manual intervention will be allowed.

You can convert the useless Excel files to csv in batch, e.g. with LibreOffice. The Excel files stay as they are, and the csv files allow consistent reading into SAS.

Re: Proc import reading numeric values as char

Kurt_Bremser — Thu, 16 Jun 2022 21:09:27 GMT

@kajal_30 wrote:
Hi Reeza,

So it can be different each time we are reading the excel file? is it better for me to convert this file into csv format and then read that file using data step and assign lengths and formats as per the final target dataset to be appended to?
Thanks
Kajal

A-B-S-O-L-U-T-E-L-Y.

It is not just the better, it is the only way to go for a production process, IMHO.

Re: Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 21:09:37 GMT

Thanks @Kurt_Bremser actually we only base sas available. do we have a base sas code for playing the same role as libre office?

Re: Proc import reading numeric values as char

Tom — Thu, 16 Jun 2022 21:10:53 GMT

You can make the check/fix step as complex as you need.

Here is a simple way, but it might still have trouble with somethings, like DATE fields.

Import the Excel sheet into a dataset.

Write the data from that dataset into a text file.

Read the text file into a dataset with the expected format. If you have an existing file that has the variables defined the way you want and in the same order as the columns in the XLSX file it is very simple.

proc import datafile='myfile.xlsx' dbms=xlsx out=step1 replace;
run;
filename csv temp;
data _null_;
  set step1;
  file csv dsd ;
  put (_all_) (+0);
run;
data want;
  if 0 then set template ;
  infile csv dsd truncover;
  input (_all_) (+0);
run;

Re: Proc import reading numeric values as char

Kurt_Bremser — Thu, 16 Jun 2022 21:19:25 GMT

@kajal_30 wrote:

Thanks @Kurt_Bremser actually we only base sas available. do we have a base sas code for playing the same role as libre office?

Not really. XLSX files are ZIP archives of XML files, so they would be quite complex to read. You may find utilities for batch MS Office conversion on the internet.

So if you don't want to (or can't) install the necessary software, request data in a reasonable format in the first place. I am quite appalled that a "professional" shop uses Excel files as a transport medium.

Re: Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 21:24:04 GMT

Hi Tom, where do we have this dataset template coming from ?

Re: Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 21:59:19 GMT

Hi Tom,

AS I am reimporting the csv to a dataset I can see 0 variables . Is there a way we can retain column names while importing from a csv file ? refraining from using input statement as in future business might change the sequence of the columns in the sheet

Regards

Kajal

Re: Proc import reading numeric values as char

Kurt_Bremser — Thu, 16 Jun 2022 22:23:04 GMT

If we're talking about production, a change in file structure must be documented before it is implemented, so your codes can be adapted. So the INPUT statement is a non-issue.

Re: Proc import reading numeric values as char

Reeza — Thu, 16 Jun 2022 22:10:19 GMT

Here's a VBS routine, SAS generated/called that converts an XLSX file to CSV.

https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e

Re: Proc import reading numeric values as char

Tom — Thu, 16 Jun 2022 22:12:33 GMT

@kajal_30 wrote:

Hi Tom,

AS I am reimporting the csv to a dataset I can see 0 variables . Is there a way we can retain column names while importing from a csv file ? refraining from using input statement as in future business might change the sequence of the columns in the sheet

Regards

Kajal

If you ran the code I posted that means the TEMPLATE dataset you created did not have any variables. Instead of a TEMPLATE dataset you can just add the code for defining the variables and reading them into the data step.

data want;
  length var1 8 var2 $20 .... varlast 8 ;
  informat var1 date.;
  format var1 date9.;
  infile csv dsd truncover;
  input var1 -- varlast ;
run;

If your XSLX files CHANGE then you need a more sophisticated process.

1) Import the XLSX file.

2) Get the list of variables from the resulting dataset.

3) Compare the list of variables to to acceptable list of variables.

4) If the file is valid the fix it. Otherwise issue an error.

Fixing it might mean using the CSV file intermediate like I posted before.

Or it might just mean reading the imported dataset and apply changes such as changing the length of character variables converting numeric variables to character or the reverse.

Re: Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 22:27:16 GMT

ok I can take it as a fair statement. So while we are doing the import from csv file the imported data without columns will follow the same sequence as we have in the file or it can be different.

for eg in csv file we have

cust_no region

100 USA

so for imported dataset can I put

input cust_no region should be ok ?

regards

Jagdeep

Re: Proc import reading numeric values as char

kajal_30 — Thu, 16 Jun 2022 22:57:48 GMT

one more concern while creating the csv file from dataset I can see there are 2 empty lines after every record. but actual excel file doesn't have any empty lines.

- importing xlsx as dataset

- exporting dataset into a csv file

May I know why am I seeing these empty lines.

regards

Kajal

Re: Proc import reading numeric values as char

ballardw — Thu, 16 Jun 2022 23:34:01 GMT

@kajal_30 wrote:

one more concern while creating the csv file from dataset I can see there are 2 empty lines after every record. but actual excel file doesn't have any empty lines.

- importing xlsx as dataset

- exporting dataset into a csv file

May I know why am I seeing these empty lines.

regards

Kajal

One of the truly obnoxious elements, in terms of data exchange, is the stuff people can put in cells.

One of them is entered using the Alt-Enter key combination. If done between visible text in cell it will force the cell to display the text with a vertical adjustment so the text after the first bit is displayed below it (may not be very obvious with long text in a narrow cell).

However, those can be entered as characters and you will not "see" anything in the spreadsheet. The exported text will usually treat those cell contents as "new lines".

There are other issues such as which operating system you are running and where/how you read the file. Conversion of files from UNIX to Windows (or vice versa) have well-known issues caused because the operating systems use different characters to tell applications that you have reached the end of a line. Windows uses two characters: Carriage Return and Line Feed (ancient names deriving from teletype), UNIX and derivatives the Line Feed (one character) . So conversion and movement can add extra characters that get treated as end-of-line.

Sometimes use of options on the INFILE statement of a data step TERMSTR can be used to force behavior that is not native to your operating system.

You will have to show 1) the code used to read that csv and 2) a couple lines of text.