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
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.
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.