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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.