BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FredGIII
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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;

FredGIII
Quartz | Level 8

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?

Reeza
Super User

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

FredGIII
Quartz | Level 8

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!

Tom
Super User Tom
Super User

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;

Ksharp
Super User

Why not split it into lots of small datasets ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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