Create lookup list

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Create lookup list

Hi

I'd like to create a lookup list of dates from an existing dataset, which can then be used in the where clause of proc sql.

Any ideas?


Accepted Solutions
Solution
‎07-23-2014 04:54 AM
Super User
Super User
Posts: 7,404

Re: Create lookup list

Well, you could have just changed the line:

     where     DATE in ("&LIST_OF_DATES.");

To

     where     put(datepart(DATE),date9.) in ("&LIST_OF_DATES.");

But glad it works for you.

View solution in original post


All Replies
Super User
Super User
Posts: 7,404

Re: Create lookup list

Hi,

What software are you using, Enterprise Guide, DM, Other?  Best to post in the specific forum for your application.

New Contributor
Posts: 3

Re: Create lookup list

SAS base programming incl. macros.

Super User
Super User
Posts: 7,404

Re: Create lookup list

Maybe post an example of what you have, what you want.  Guessing from what you have said:

proc sql;

     select     DATES

     into          :LIST_OF_DATES separated by '","'   /* Note single quote, then double */

     from       DATASET;

quit;

proc sql;

     create table WANT as

     select     *

     from       HAVE

     where     DATE in ("&LIST_OF_DATES.");

quit;

New Contributor
Posts: 3

Re: Create lookup list

Hi

The issue lies within the where clause  - the formats don't match

": Expression using IN has components that are of different data types."

The original dates are in datetime formats.

I manage to solve it by manipulating the formats - creating date2 from the original date in the table:

DATA datelist;

SET datelist (Keep=DateORIG);

Date2= catt("'",put(DateORIG,DATE9.),"'d");

RUN;

PROC SQL;

SELECT DATE2  INTO :LIST_OF_DATES separated by ','  FROM     Datelist;

QUIT;

PROC SQL;

CREATE TABLE CX AS SELECT * FROM MAINTABLE

WHERE datepart(DateORIG) IN (&List_OF_DATES.);

QUIT;

Thanks for your suggestion though - it got me halfway there.

Solution
‎07-23-2014 04:54 AM
Super User
Super User
Posts: 7,404

Re: Create lookup list

Well, you could have just changed the line:

     where     DATE in ("&LIST_OF_DATES.");

To

     where     put(datepart(DATE),date9.) in ("&LIST_OF_DATES.");

But glad it works for you.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 231 views
  • 3 likes
  • 2 in conversation