topic Re: Importing excel file with all variables as character. in SAS Procedures

Importing excel file with all variables as character.

SWEETSAS — Thu, 17 May 2018 08:02:05 GMT

Hello,

I have an excel file I like to read into SAS. The dataset contains many variables that are both numeric character values, so I like to read the variables in the dataset as character. I saw some post that can enable read only one variable as character. But the dataset contains many variables that it will be challenging to list them all, so I want to read all variables as character.

eg:

data xx;

input a b c d;

datalines;

2 <llq 4 6

<llq 4 5 3

4 6 <llq 4

5 6 3 1

;

run;

Any help will be appreciated:

Re: Importing excel file with all variables as character.

RW9 — Thu, 17 May 2018 08:18:09 GMT

Unfortunately Excel is a poor data medium (i.e. it is not well structured like a dataset or database) and I suppose you are using proc import - which is a guessing procedure. There is no simple switch to allow you read all in as character as far as I am aware. One easy solution is to add one row to you Excel file with "AAAAA" in each cell of each column (Select row, shift+right fill). This way proc import will always "guess" the data to be character.

Another alternative is to save to CSV file, then proc import that, in the log it will give you the code which it has used to import the file. You can then modify that to match the formats and lengths you want - this is the best method for getting data in as it is accurate, repeatable and fully under you (as the data owners) control.

A final method I can think of would be to write a VBA macro in Excel which writes out the Excel file to CSV, with all the data enclosed in double quotes.

But I suppose the real question here is why you want them all character, that data looks like lab results to me, therefore it would be far easier to work with in a normalised structure (E.g. CDISC LAB, or LB domain format).

Re: Importing excel file with all variables as character.

Kurt_Bremser — Thu, 17 May 2018 08:22:42 GMT

Standard answer: save to csv, import with data step. The only way to guarantee consistent repeatable results.

Re: Importing excel file with all variables as character.

SWEETSAS — Thu, 17 May 2018 09:10:33 GMT

Thank you very much for your prompt and detailed reply.
Yes, it's lab data. The reason I want to read all variable in as character is that after reading the data, I can impute the LLQ and then multiply the variables by I to convert them to numeric.
That is the only way I can think of handling the issue.

Re: Importing excel file with all variables as character.

RW9 — Thu, 17 May 2018 09:20:43 GMT

There is nothing stopping you doing imputation or anything with a normalised data structure. Let me show with the data you provided;

data xx;
  input char_result $ @@;
  numeric_result=ifn(char_result="<llq",999,input(char_result,best.));
datalines;
2    <llq  4  6
<llq 4    5   3
4    6  <llq  4
5   6   3   1
;
run;

In this way I retain the original data, and the converted numeric variant. This is how CDISC models work by having both.

Re: Importing excel file with all variables as character.

SWEETSAS — Thu, 17 May 2018 09:35:37 GMT

Thank you very much. But the data set is in excel which I will convert to csv per you initial reply.
I gave example with input statement to show how the data looks like.
I will appreciate a code for how I can read in the CSV format into SAS assumming 5 variables four of which have mixed variable type.
Thanks again.

Re: Importing excel file with all variables as character.

RW9 — Thu, 17 May 2018 09:57:15 GMT

Its the same to read datalines of text file, which csv is:

data xx;
  infile "....csv";
  input a $ b $ c $ d$ e$;
  array c{5} a b c d e;
  array n{5} a_n b_n c_n d_n e_n;
  do i=1 to 5;
    n{i}=ifn(c{i}="<llq",999,input(c{i},best.));
  end;
run;

But you see from that how much more effort it is to work with transposed data.

Re: Importing excel file with all variables as character.

Quentin — Thu, 17 May 2018 14:33:24 GMT

I think it depends *how* you import the Excel file. Some engines (such as PCFILES server) support the DBSASTYPE option which allows you to force that data be read from Excel as character.

I submitted a SASware ballot idea to add such an option for the XLSX engine:

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-DBSASTYPE-or-similar-option-to-XLSX-engine-to-force-variable/idi-p/453938