DATA Step, Macro, Functions and more

Array looping through dates

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Array looping through dates

Hello all,

 

I am trying to loop dates so that it returned something like this:

 

31jan2015:23:59:59.000

28feb2015:23:59:59.000

31mar2015:23:59:59.000

.

.

.

31jul2016:23:59:59.000

 

The code actually does not work:

 

data _null_;

do i = 1 to 19;

a = put(intnx('month',intnx('month',today(),-i),0,'end'),date9.);

call symput ('d',a||':23:59:59.000');

put "&d.";

end;

run;

 

Any suggestions? Thanks

 

Jiri

 


Accepted Solutions
Solution
‎08-18-2016 05:32 AM
Super User
Posts: 6,942

Re: Array looping through dates

Macro calls (everything starting with a & or %) are expanded immediately when encountered in the program text. So SAS tries to find the macro variable d before the data step (that creates a macro variable with call symput during runtime) is compiled and executed.

Try this for clarification:

data _null_;
do i = 1 to 19;
a = put(intnx('month',intnx('month',today(),-i),0,'end'),date9.);
call symput ('d',a||':23:59:59.000');
end;
run;
%put "d=&d.";

Since you seem to want to store a list of dates for later actions, it is better to store them in a dataset and use that later.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎08-18-2016 05:32 AM
Super User
Posts: 6,942

Re: Array looping through dates

Macro calls (everything starting with a & or %) are expanded immediately when encountered in the program text. So SAS tries to find the macro variable d before the data step (that creates a macro variable with call symput during runtime) is compiled and executed.

Try this for clarification:

data _null_;
do i = 1 to 19;
a = put(intnx('month',intnx('month',today(),-i),0,'end'),date9.);
call symput ('d',a||':23:59:59.000');
end;
run;
%put "d=&d.";

Since you seem to want to store a list of dates for later actions, it is better to store them in a dataset and use that later.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 44

Re: Array looping through dates

Thanks for your help!

 

Jiri

Super User
Posts: 6,942

Re: Array looping through dates

And compare my previous post with this (look into the log):

data _null_;
do i = 1 to 19;
a = put(intnx('month',intnx('month',today(),-i),0,'end'),date9.);
d = a||':23:59:59.000';
put d=;
end;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 44

Re: Array looping through dates

I tried to run the code with your amendment but it for some reason fails to remember i's values. Any further suggestions? Thanks again

Super User
Posts: 6,942

Re: Array looping through dates


Uknown_user wrote:

I tried to run the code with your amendment but it for some reason fails to remember i's values. Any further suggestions? Thanks again


This very much depends on what you want to do with your list of dates and index numbers.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,681

Re: Array looping through dates

data _null_;
do i = 1 to 19;
a = intnx('dtmonth',datetime(),-i,'end');
put a= datetime32.3;
end;
run;

Contributor
Posts: 44

Re: Array looping through dates

Thx for all suggestions. I used this code:

 

data _null_;

do i = 2 to 20;

a = intnx('dtmonth',datetime(),-i,'end');

call symput('a',put(a,datetime32.3));

end;

run;

%put a = &a;

 

However, the created list contains just one value. It says that macro variable a resolves just the last i's value:

 

"Macro variable A resolves to 31DEC2014:23:59:59.000"

 

I cannot get how come it does not store all dates?

 

Any tips? thx

 

Jiri

 

Super User
Posts: 10,500

Re: Array looping through dates


 


The call symput named every value with the same macro variable name. Also, you only requested to see one value. You would have to assign multi macro variable names and then a different %put (or other approach) to see them.

 

Look at:

data _null_;
   length aname $ 10;
   do i = 2 to 20;
      aname = cats('a',i);
      a = intnx('dtmonth',datetime(),-i,'end');
      call symput(aname,put(a,datetime32.3));
   end;
run;
%put &a2 &a3 &a4 &a20;
Contributor
Posts: 44

Re: Array looping through dates

[ Edited ]

Thx, is there any way to store all values from the created list of dates and work with them as with single object (one macro variable)? Writing down all values might be complicated when the range gets bigger. When I put object "aname" from your code into macro variable:

 

%put a = &aname;

 

It says:

 

"WARNING: Apparent symbolic reference ANAME not resolved."

 

I am not quite sure how to work with arrays but it could help this problem?

 

Thanks for suggestions.

 

Jiri

Super User
Posts: 10,500

Re: Array looping through dates

%put &aname fails because you did not create a MACRO varaible aname. You would have had to have a call symput("aname", ) for that.

 

Without knowing your exact useage it is a bit difficult to say what to put in the list. For instance if you want to use the values later such as this:

 

if somevariable in (<macro list>) then do ....

 

Then the values of the elements of the macro list probably should not be formatted datetimes as then you have to add quotes and DT to indicate they are date literals.

 

Here is one way to build a list that is comma separated, remove the comma from the "separated by ', ' " if you want spaces.

data temp;
   length aname $ 10;
   do i = 2 to 20;
      a = intnx('dtmonth',datetime(),-i,'end');
      output;
   end;
run;

proc sql noprint;
   select put(a,datetime32.3)  into : a separated by ', '
   from temp
   ;
quit;

%put &a;

Note the addition of creating a dataset and the output statment.

 

This approach may be the easiest overall for a single list because it does not requie pre-calculating the length of the character value that would be needed in the data step approach. There is a default maximum size for the length of a single macro variable do consider. If you are generating enough of these to approach the 65,000 characters your approach should be reconsidered.

 

If you describe what you are actually going to do with this list it would help as I suspect you are going to cause yourself more work than needed.

Contributor
Posts: 44

Re: Array looping through dates

Thanks for your reply. I am trying to produce the list of dates:

 

30JUN2016:23:59:59.000

31MAY2016:23:59:59.000

30APR2016:23:59:59.000

31MAR2016:23:59:59.000

29FEB2016:23:59:59.000

31JAN2016:23:59:59.000

31DEC2015:23:59:59.000

30NOV2015:23:59:59.000

31OCT2015:23:59:59.000

30SEP2015:23:59:59.000

31AUG2015:23:59:59.000

31JUL2015:23:59:59.000

30JUN2015:23:59:59.000

31MAY2015:23:59:59.000

30APR2015:23:59:59.000

31MAR2015:23:59:59.000

28FEB2015:23:59:59.000

31JAN2015:23:59:59.000

31DEC2014:23:59:59.000

 

which will consequently source the macro (each date should be one macro variable, so in the first step it goes for ... then ..., etc) . The format of datetime is required by the macro it self. Any suggestions how to aporoach this? Thanks

 

Super User
Posts: 6,942

Re: Array looping through dates

%let num_months=19;

data temp;
length datetime_value $ 10;
do i = 0 to &num_months - 1;
  datetime_value = intnx('dtmonth',datetime(),i,'end');
  output;
end;
run;

data _null_;
set temp;
call execute('%your_macro('!!put(datetime_value,best.)!!')');
run;

You only have to set the number of months, everything else happens dynamically, and the code is rather simple.

Anytime you need to work through a list of values, consider storing them in a dataset and working from that.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 44

Re: Array looping through dates

Thanks for help!

 

Jiri

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 487 views
  • 2 likes
  • 4 in conversation