Variable length changing

[Pandu2]

Hi All,
I hope everyone is doing great.
I've two variables named X, Y whose types are X - numeric of length 8
Y - Date of length 8. However whenever there's empty rows in both the variables the type and length automatically changing to Character 1. May I know how to resolve this issue. Even if the rows are empty the types should be of numeric and date of length 8. Thankyou!.

[JosvanderVelden]

When does this happen? Does it happen when importing from excel (xls or xlsx) with 'mixed' columns?

[Pandu2]

Exactly.

[Kurt_Bremser]

It's a problem with using Excel files. It can only be avoided by saving the data to text files (comma, semicolon- or tab-separated) which are then read with a custom written data step, so the variable attributes will always be the same.

[Tom]

---

@Pandu2 wrote:
Hi All,
I hope everyone is doing great.
I've two variables named X, Y whose types are X - numeric of length 8
Y - Date of length 8. However whenever there's empty rows in both the variables the type and length automatically changing to Character 1. May I know how to resolve this issue. Even if the rows are empty the types should be of numeric and date of length 8. Thankyou!.

---

Variables do not change on their own.  Please show examples of the code you are running that is causing this behavior.

Even if you are creating a dataset using a guessing procedure like PROC IMPORT a single empty cell is not going to convert a column that is otherwise numeric to character of length 1.  But cell with a non-numeric value would cause PROC IMPORT to define the variable as character. Also a totally empty column could cause PROC IMPORT to define the variable as character of length 1.

[Pandu2]

Actually I'm importing a particular sheet of an excel file into sas. Regarding that sheet sometimes two columns in that sheet might be empty or sometimes it has data in it. When the columns has data in it the types of those columns are date8. & Numeric 8. When there's no data in those two columns sas by default assigning the lenght and type as Character 1. So, could you please help me how to fix this. Even if the columns has empty values in it or not the type of those two columns shouldn't be changed.

My code :

Proc import datafile="D:\Users\12345\Desktop\xyz.xlsx"
DBMS = xlsx
Replace
Out=work.xyz_1;
Sheet="sales";
Run;

Note: I used my desktop location for the input excel file to import but sometimes I use my sas server location.

[Kurt_Bremser]

As I already said, don't use Excel files where SAS has to guess about column attributes with every new file.

Save to text files from Excel, read those with a data step you write once and use for every import. Do NOT use PROC IMPORT.

[Pandu2]

May I know how to save an excel file to text file where it has different sheets inside. Please provide an example. Thankyou.

[Kurt_Bremser]

Every sheet needs to be saved to its own file. Just do "File - Save as".

[Pandu2]

Appreciate your help but I have those Excel files in server. From there on I import into sas.

[Patrick]

---

@Pandu2 wrote:
Appreciate your help but I have those Excel files in server. From there on I import into sas.

---

With Excel every single cell in a column can have a different "data type" - like first cell is a string, 2nd a data and third some number. 

With SAS tables as with any database the data type for a column can't change. 

When SAS imports an Excel sheet it "guesses" the required data type based on the cell values in this column. But if there isn't any value (all blank) then SAS will default to character. That's what you observe and with the xlsx engine there isn't a way to force a specific data type.

It's ugly but you could eventually use Proc Import to read the Excel sheet, then right away Proc Export to create a delimited text file and then read this text file via a SAS data step where you have full control over how you read the data and what data type gets created.

...and you might be motivated to upvote Add DBSASTYPE or similar option to XLSX engine to force variable types

[Pandu2]

Thankyou so much for providing much info. I've exported that excel file to text file with a tab delimiter. However, I'm not sure how to read this text file via data step. I request you to please provide me an example for this.

[Kurt_Bremser]

data want;
infile "path to your file" dlm="09"x dsd truncover firstobs=2;
input
  /* specify your variables and informats here, use the colon modifier for the informats */
;
format
  /* specify display formats as needed, for numbers and stuff like dates and times */
;
run;

This is the basic method to read a tab-delimited file which has a header line.

[ballardw]

If this is related to Proc Import then the file type and your import code are IMPORTANT for "how to fix".

So share the code.

Note that if you are repeatedly using Proc Import on XLSX then you are going to have other differences. Variables that are not missing can be different types because of the values in different files, lengths of character variable will change and it is not impossible for the variable names to change.

To read multiple files that should have the same layout the Data step is the preferred tool which means that converting the files to a text delimited format will be needed. Or you get to write lots of "fix it" code that changes for each file.