Date Imports into SAS from Excel

[sg_kr]

HI, 

i am using the below code to import the excel file with date.

proc import file="&FM.\01Source\IMG YTD &IDBYear..xlsx"
OUT=EQ (keep=A--I)
DBMS=XLSX REPLACE;
SHEET="&IDBYear. - YTD";
GETNAMES=NO;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
run;

data rwork.EQ (drop=A--I);
set EQ;

/*Delete unwanted columns*/
if B="" then delete;
if C="" then delete;
if upcase(C) in ("MARKET","") then delete;

format TRANSdate mmddyy10.;
format old_polno $13.;
format MARKET $10.;
format clientnme $50.;
format transamt dollar10.2;
format new_polno $13.;

TRANSdate = input(compress(A), mmddyy10.);
old_polno = input(compress(B), $13.);
market = input(compress(C), $10.);
clientnme = input(compress(D), $50.);
transamt = input(compress(E), dollar10.2);
new_polno = input(compress(I), $13.);

if transamt in (0, .) then delete;
if new_polno in (0, .) then delete;
run;

after importing through proc import, its showing as number like sas Days format

imported Excel Format:

1/11/2019

1/15/2019

1/16/2019

1/16/2019

1/17/2019

1/22/2019

1/24/2019

1/30/2019

but when i look into the EQ dataset(second datastep) its showing null(attached screenshot)

please help me on this.

thanks

[Kurt_Bremser · Accepted solution]

The usual message for fixing all your woes:

save the data to csv, and read it with a custom data step where you have total control over variable names, attributes and how the content is read. In one step.

The Excel file format is useless for data transfer.

[tomrvincent]

Excel works just fine. 

I find that if I bring in a date as text I can then validate it and convert it. So bring transdate in as $10. and then use these examples:

case when Date_Received is not missing and input(Date_Received,best10.) is missing then 'bad received date' else 'clean' end as invalid5,

/* convert from excel date to SAS date */
input(Date_Received,best10.)-21916 as Received_Dt format date9.,

[ballardw]

Your attachment doesn't appear (yet).

The SAS DELETE as you are using it removes entire rows, not columns. Perhaps that is the issue.

But better would be to read a CSV with more control than to "fix" things caused by the limited guessing ability of Proc Import as @Kurt_Bremser suggests.