topic list of dates in macro variable list - SQL where date = in SAS Programming

list of dates in macro variable list - SQL where date =

FredGIII — Wed, 12 Jun 2013 20:02:22 GMT

I have a very large time series dataset where I am trying to generate plots grouped by day. Because of the size of the data, I cannot do BY without running out of memory. So my thought was to create a macro that cycles through distinct dates and creates temporary subsets of data by day, then creates various plots by day.

My dataset key fields:

datetime date valueX

23APR13:14:47:00 2013-04-23 48.6

My Code:

proc sql noprint;
     select distinct date /* also tried DATEPART(datetime)FORMAT=YYMMDD10. */
     INTO :date_list separated by ","
     from myDataset;
quit;

/* if I use %PUT &date_list, the data looks correct - matches the date and format of the date field */

%Let i =1;

/* Will do a macro do while loop, but have to get the first proc sql working first */

%PUT %scan(%bquote(&date_list), &i, %str(,));

proc sql;
create table work._temp as
select datetime, date, valueX
from myDataset
where date = %scan(%bquote(&date_list), &i, %str(,))
order by datetime;
quit;

The proc sql query runs but 0 rows returned. Obviously the date is not matching the %scan. But when you look individually, it appears as if they match. I have tried a number of other formatting options. Any suggestions?

Thanks,

Fred

Re: list of dates in macro variable list - SQL where date =

Reeza — Wed, 12 Jun 2013 20:35:18 GMT

The problem may be specifying the dates in a format that SAS understands, e.g the date literal format:

proc sql;

select distinct date format=date9.

into :date_list separated by ","

from sashelp.stocks;

quit;

%put &date_list;

Re: list of dates in macro variable list - SQL where date =

FredGIII — Wed, 12 Jun 2013 20:59:36 GMT

Reeza,

I changed the format as you suggested to pull the distinct list, but still having issues matching the literal format in the follow up query.

I changed the follow up query to :

proc sql;

create table work._temp as

select datetime, date FORMAT=date9., valueX

from myDataset

where date = %scan(%bquote(&date_list), &i, %str(,))

order by datetime;

quit;

and I get the error:

NOTE: Line generated by the macro function "SCAN".

1 24APR2013

-------

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>,

=, >, >=, AND, EQ, EQT, GE, GET, GROUP, GT, GTT, HAVING, LE, LET, LT, LTT, NE,

NET, OR, ^=, |, ||, ~=.

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

Can you not put dates in macro variable list?

Re: list of dates in macro variable list - SQL where date =

Reeza — Wed, 12 Jun 2013 21:02:56 GMT

You can but this line of the code:

where date = %scan(%bquote(&date_list), &i, %str(,))

isn't appropriate SAS syntax.

It translates to:

where date= 01Apr2011

and and needs to be

where date="01Apr2011"d

I don't know if you can just wrap it or need to grab the variable ahead of time.

1. where date="%scan(%bquote(&date_list), &i, %str(,))"d

2. %let date_current=%scan(%bquote(&date_list), &i, %str(,));

where date="&date_current"d

Re: list of dates in macro variable list - SQL where date =

FredGIII — Wed, 12 Jun 2013 21:10:59 GMT

Whew, that did it!

proc sql;

create table work._temp as

select datetime, date FORMAT=date9., valueX

from myDataset

where date = "%scan(%bquote(&date_list), &i, %str(,))"d

order by datetime;

quit;

I am finding that dealing with date and time formats within SAS is a royal pain in the ....... Thanks for your suggestions!

Re: list of dates in macro variable list - SQL where date =

Tom — Wed, 12 Jun 2013 21:34:41 GMT

If you are just going to use them to drive a WHERE clause there is no need for them to be human readable.

...

select distinct date format=10. into :date_list separated by ' '

...

%let n=&sqlobs;

%do i=1 %to &n;

...

where date=%scan(&date_list,&i)

...

%end;

Re: list of dates in macro variable list - SQL where date =

Ksharp — Mon, 17 Jun 2013 06:10:32 GMT

Why not split it into lots of small datasets ?