Import Excel with where condition for date using SAS DI

[ashna]

Hello,

I need to import an excel file with a where condition saying pull only col a with dateformat .

Example : I have a table with 3 columns as follows

Col A               Col B                 Col C

23/11/2018      Cars                  2000

24/10/2018      Buses               4000

Null 

                        test                  600          

When I import the above table into sas, I need to pull in data where Col A is non-empty and Col A has only dates.

proc import datafile =filepath 
out=work.input (where=(Not Missing(A) & A=date9.)) 
dbms=xlsx replace;
getnames=No;
run;

Can someone suggest best way to do this.

Thanks in advance all the help you provide 🙂

[SuryaKiran · Accepted solution]

PROC IMPORT might not be the right way to do this, because it imports data guessing the rows. It may sometimes read date values as character. 

First import the data and then using a datastep or proc then modify your dataset. Since you have 'Null' as value I guess SAS will import that field as character. Convert the character to numeric and SAS will identify the format mentioned and gives missing if not valid.

proc import datafile =filepath 
out=work.input
dbms=xlsx replace;
getnames=No;
run;


data work.input;
set work.input(rename=(A=A_));
A=input(A_,ddmmyy10.); /* Which are not in ddmmyy format will have missing values */
if not missing(A);
run;

[Reeza]

That may also result in a really messy log....