topic Re: Could not able to read data correctly. in SAS Programming

Could not able to read data correctly.

vishyy — Sat, 08 Apr 2017 09:42:10 GMT

I have a excel file which has many sheet.

Have imported data with proc import mentioning sheet as "day" and dbms as "excel".

I have sucessfylly imported data in SAS but there exist a problem with one of my variable that is "ELIGIBLE"

The variable "ELIGIBLE" ahs two values TRUE and FALSE.

while importing SAS shows 0 and * as value.

while cheking the column properties its showing Type as Numeric , Lenght as 8 , format and informat as 1.

while checking the logs for proc import it also doesnot have the procedure import step.

Thanks in advance

Vishyy

Re: Could not able to read data correctly.

Patrick — Sat, 08 Apr 2017 10:25:20 GMT

Even though some people don't like Excel attachments for security reasons, I believe this is a case where you should provide your Excel with a few rows in it as well as your Proc Import code.

What's happening here depends very much on how this "boolean" fields are defined in Excel.

Re: Could not able to read data correctly.

rogerjdeangelis — Sat, 08 Apr 2017 12:21:06 GMT

Could not able to read data correctly.

This type of issue is best solved on the excel side using passthru.

inspired by
https://goo.gl/T7D0Nc
https://communities.sas.com/t5/Base-SAS-Programming/Could-not-able-to-read-data-correctly/m-p/348388


HAVE
====

d:/xls/trufls.xlsx

 +------+-----------+
 |      |    A      |
 +------+-----------+
 |      |           |
 |    1 |  ELIGIBLE |
 |    2 |   TRUE    |
 |    3 |   FALSE   |
 |    4 |   TRUE    |
 |    5 |   FALSE   |
 |    6 |   TRUE    |
 | ...  |   ...     |
 +------------------+

 [SHEET1]

WANT  (SAS dataset DAY)
=======================

 Up to 40 obs WORK.DAY total obs=5

 Obs    ELIGIBLE

  1        1
  2        0
  3        1
  4        0
  5        1

WORKING CODE
===========

      select * from connection to Excel
      iif(eligible="TRUE",1,'0') as eligible

FULL SOLUTION
=============

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;

%utlfkil(d:/xls/trufls.xlsx);
libname xel "d:/xls/trufls.xlsx";
data xel.day;
  do eligible='TRUE ' ,'FALSE' ,'TRUE' ,'FALSE' ,'TRUE';
     output;
  end;
run;quit;
libname xel clear;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

/* [day$] if not named range */

proc sql dquote=ansi;
  connect to excel (Path="d:\xls\trufls.xlsx" mixed=yes);
    create
        table day as
    select * from connection to Excel
        (
         Select
            iif(eligible="TRUE",1,'0') as eligible
         from
           [day$]
        );
    disconnect from Excel;
Quit;

Re: Could not able to read data correctly.

rogerjdeangelis — Sat, 08 Apr 2017 12:26:43 GMT

Could not able to read data correctly.

This type of issue is best solved on the excel side using passthru.

inspired by
https://goo.gl/T7D0Nc
https://communities.sas.com/t5/Base-SAS-Programming/Could-not-able-to-read-data-correctly/m-p/348388


HAVE
====

d:/xls/trufls.xlsx

 +------+-----------+
 |      |    A      |
 +------+-----------+
 |      |           |
 |    1 |  ELIGIBLE |
 |    2 |   TRUE    |
 |    3 |   FALSE   |
 |    4 |   TRUE    |
 |    5 |   FALSE   |
 |    6 |   TRUE    |
 | ...  |   ...     |
 +------------------+

 [SHEET1]

WANT  (SAS dataset DAY)
=======================

 Up to 40 obs WORK.DAY total obs=5

 Obs    ELIGIBLE

  1        1
  2        0
  3        1
  4        0
  5        1

WORKING CODE
===========

      select * from connection to Excel
      iif(eligible="TRUE",1,'0') as eligible

FULL SOLUTION
=============

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;

%utlfkil(d:/xls/trufls.xlsx);
libname xel "d:/xls/trufls.xlsx";
data xel.day;
  do eligible='TRUE ' ,'FALSE' ,'TRUE' ,'FALSE' ,'TRUE';
     output;
  end;
run;quit;
libname xel clear;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

/* [day$] if not named range */

proc sql dquote=ansi;
  connect to excel (Path="d:\xls\trufls.xlsx" mixed=yes);
    create
        table day as
    select * from connection to Excel
        (
         Select
            iif(eligible="TRUE",1,'0') as eligible
         from
           [day$]
        );
    disconnect from Excel;
Quit;

Re: Could not able to read data correctly.

Tom — Sat, 08 Apr 2017 13:25:30 GMT

The '*' means that the actual value cannot be displayed in 1 character. Remove the format from the variable if you want to see the values that were actually imported.

data _null_;
  set have ;
  if eligible ne 0 then put eligible = best32. ;
run;

You can also try using the XLSX (or XLS) engine on a LIBNAME instead of using PROC IMPORT and see if the values are converted differently.