BookmarkSubscribeRSS Feed
tyi7689okt
Calcite | Level 5

I am trying to automate a program that compares today's data with data from last year's closest available date.

To do this I have a list of all the days we have data last year, and I have it loop through creating a list of distances from today's date.  I then calculate the minimum of these values. 

It works fine running it in SAS windows or EG, but when I try to run it in batch mode it crashes because the line of distances is too long.

I am trying to add a enter after every ten iterations, so the line is shorter.  However I can't figure out how to concatenate using ENTER as a delimiter.  Here is my code, the part in bold is pseudo-code of what I want to happen: 

 

%let last_year=13DEC2016 21DEC2016 20JAN2017 07FEB2017
22FEB2017 07MAR2017 21MAR2017 28MAR2017 04APR2017 07APR2017 10APR2017 11APR2017
12APR2017 13APR2017 14APR2017 17APR2017 18APR2017 19APR2017 21APR2017 24APR2017
26APR2017 27APR2017 02MAY2017 03MAY2017 04MAY2017 05MAY2017 08MAY2017 09MAY2017
10MAY2017 11MAY2017 15MAY2017 16MAY2017 17MAY2017 18MAY2017 19MAY2017 22MAY2017
23MAY2017 24MAY2017 25MAY2017 26MAY2017 30MAY2017 31MAY2017 01JUN2017 06JUN2017
07JUN2017 14JUN2017 15JUN2017 16JUN2017 19JUN2017 20JUN2017 21JUN2017 22JUN2017
23JUN2017 26JUN2017 27JUN2017 28JUN2017 29JUN2017 30JUN2017 03JUL2017 05JUL2017
06JUL2017 10JUL2017 11JUL2017 12JUL2017 13JUL2017 14JUL2017 18JUL2017 19JUL2017
20JUL2017 25JUL2017 26JUL2017 27JUL2017 28JUL2017 31JUL2017 01AUG2017 02AUG2017
03AUG2017 04AUG2017 07AUG2017 08AUG2017 09AUG2017 10AUG2017 11AUG2017 15AUG2017
16AUG2017 17AUG2017;

 

%let distlist0=;
%let total_days=%sysfunc(countw(&last_year.));
%let lastyrday=%sysevalf(%sysfunc(today())-365);

 

%macro histdate;
   %global distlist&total_days.;
   %do i=1 %to &total_days.;
       %let j=%sysevalf(&i.-1);
       %let date&i.=%sysevalf("%sysfunc(scan(&last_year.,&i.))"d);
       %let dist&i.=%sysfunc(abs(&lastyrday.-&&date&i..));
       %let distlist&i.=%sysfunc(catx(%str(,),&&distlist&j..,&&dist&i..));
       %if %sysfunc(mod(&i.,10))=0 %then %let distlist&i.= &&distlist&i.. || ENTER;
   %end;
%mend;
%histdate;

 

%let mindate=%sysfunc(min(&&distlist&total_days..));
%let minnum=%sysfunc(findw("&&distlist&total_days..",&mindate.,%str(,),%str(E)));
%let trimnum=%sysfunc(trim(&minnum.));
%let hist_date=%sysfunc(scan(%str(&last_year.),&trimnum.));

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Smiley Frustrated

 

Put your data in a dataset that is what they are used for.  If you can give some real example of test data in the form of a datastep and what you want out I will give you the code.  Macro is not there to do data processing, it is a find/replace system.  Base SAS is where you do processing as that as the data types, functions etc. and is the effectual compiled end product.

Kurt_Bremser
Super User

DO NOT HANDLE DATA IN MACRO LANGUAGE!

 

This is best solved by keeping a dataset with all pertinent information for the dates.

 

See this example:

data mydates;
input mydate date9.;
format mydate yymmddd10.;
cards;
13DEC2016
21DEC2016
20JAN2017
07FEB2017
22FEB2017
07MAR2017
21MAR2017
28MAR2017
04APR2017
07APR2017
10APR2017
11APR2017
12APR2017
13APR2017
14APR2017
17APR2017
18APR2017
19APR2017
21APR2017
24APR2017
26APR2017
27APR2017
02MAY2017
03MAY2017
04MAY2017
05MAY2017
08MAY2017
09MAY2017
;
/* lots of dates omitted, to keep code short */
run;

/* Find closest data to today's date 1 year ago */
data compare;
set mydates;
distance = abs(intck('days',intnx('year',today(),-1,'s'),mydate));
run;

proc sort data=compare;
by distance;
run;

data _null_;
set compare (obs=1);
call symputx('hist_date',put(mydate,best.));
put mydate date9.;
run;
gamotte
Rhodochrosite | Level 12

Hello,

 

proc sql;
SELECT *, intnx("year",today(),-1,"s") AS oneYearAgo format=date9., abs(intck("day",dt,intnx("year",today(),-1,"s"))) AS distance
FROM HAVE
HAVING distance=min(distance)
;
quit;

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
  • 3 replies
  • 795 views
  • 5 likes
  • 4 in conversation