BookmarkSubscribeRSS Feed
vishyy
Obsidian | Level 7

Hi

 

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

 

 

4 REPLIES 4
Patrick
Opal | Level 21

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.

rogerjdeangelis
Barite | Level 11
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;

rogerjdeangelis
Barite | Level 11

 

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;

 

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1012 views
  • 0 likes
  • 4 in conversation