DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

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

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


Accepted Solutions
Solution
‎06-12-2013 05:02 PM
Super User
Posts: 17,828

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

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

View solution in original post


All Replies
Super User
Posts: 17,828

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

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;

Contributor
Posts: 50

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

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

            -------

        22

        76

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?

Solution
‎06-12-2013 05:02 PM
Super User
Posts: 17,828

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

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

Contributor
Posts: 50

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

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!

Super User
Super User
Posts: 6,500

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

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;

Super User
Posts: 9,681

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

Why not split it into lots of small datasets ?

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 1804 views
  • 2 likes
  • 4 in conversation