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
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
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;
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?
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
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!
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;
Why not split it into lots of small datasets ?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.