HOW TO USE 'WHERE' CLAUSE IN FILE READER TRANSFORMATION IN SAS-DI

Reply
New Contributor
Posts: 4

HOW TO USE 'WHERE' CLAUSE IN FILE READER TRANSFORMATION IN SAS-DI

Hi All !

          Can anyone please suggest me on operation of WHERE clause format in FILE READER Transformation -> Advanced Options -> WHERE condition.

I have an i/p column == CLIENT_ID

and i have to pass only those CLIENT_ID which are not null.

So, how should i frame it in the WHERE clause Box

eg. tried but didnot work

CLIENT_ID ^= .

CLIENT_ID IS NOT NULL

Thanks,

Ishan Khobare

Super Contributor
Posts: 349

Re: HOW TO USE 'WHERE' CLAUSE IN FILE READER TRANSFORMATION IN SAS-DI

Hi,

where condition in file reader transformation only works for sas data sets.

So you can use an extract transformation/data validation transformation after file reader transformation to remove missing values.

Thanks,

Shiva

New Contributor
Posts: 4

Re: HOW TO USE 'WHERE' CLAUSE IN FILE READER TRANSFORMATION IN SAS-DI

hi shivas,

                   Thanks for the reply.. Actually i had tried with extract initially but the requirement was of usin FILE READER ... So i was finding it tough to explore....Thanks a lot!

Regards,

Ishan

Super Contributor
Posts: 408

Re: HOW TO USE 'WHERE' CLAUSE IN FILE READER TRANSFORMATION IN SAS-DI

Ishan,

The WHERE works but you have to define the target table of the transformation as a table, not a view. That's because the WHERE clause ends up in the data statement of the generated data step:

data work.W4SBCVT  

   (WHERE = (rectype='01'))

;

If the target is a view (default!) then this is generated, which throws a syntax error:

134    data work.W4SBCVT / view = work.W4SBCVT
135       (WHERE = (rectype='01'))
           _____ _
           2222
           202   76

ERROR 22-322: Syntax error, expecting one of the following: ALTER, EXECUTE, PROTECT, PW, READ, SOURCE, SRC, WRITE. 

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

Regards Jan.

Ask a Question
Discussion stats
  • 3 replies
  • 448 views
  • 1 like
  • 3 in conversation