topic Re: Import Excel with where condition for date using SAS DI in New SAS User

Import Excel with where condition for date using SAS DI

ashna — Mon, 05 Nov 2018 18:36:27 GMT

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 🙂

Re: Import Excel with where condition for date using SAS DI

SuryaKiran — Mon, 05 Nov 2018 18:55:37 GMT

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;

Re: Import Excel with where condition for date using SAS DI

Reeza — Mon, 05 Nov 2018 21:32:10 GMT

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