topic Re: Array looping through dates in SAS Programming

Array looping through dates

Uknown_user — Thu, 18 Aug 2016 09:12:52 GMT

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

Re: Array looping through dates

Kurt_Bremser — Thu, 18 Aug 2016 09:31:52 GMT

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.

Re: Array looping through dates

Uknown_user — Thu, 18 Aug 2016 09:33:06 GMT

Thanks for your help!

Jiri

Re: Array looping through dates

Kurt_Bremser — Thu, 18 Aug 2016 09:33:45 GMT

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;

Re: Array looping through dates

Uknown_user — Thu, 18 Aug 2016 09:41:41 GMT

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

Re: Array looping through dates

Ksharp — Thu, 18 Aug 2016 10:09:41 GMT

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

Re: Array looping through dates

Kurt_Bremser — Thu, 18 Aug 2016 10:11:11 GMT

@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.

Re: Array looping through dates

Uknown_user — Thu, 18 Aug 2016 14:24:49 GMT

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

Re: Array looping through dates

ballardw — Thu, 18 Aug 2016 14:33:58 GMT

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;

Re: Array looping through dates

Uknown_user — Thu, 18 Aug 2016 15:02:57 GMT

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

Re: Array looping through dates

ballardw — Thu, 18 Aug 2016 15:24:18 GMT

%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.

Re: Array looping through dates

Uknown_user — Fri, 19 Aug 2016 06:54:12 GMT

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

Re: Array looping through dates

Kurt_Bremser — Fri, 19 Aug 2016 07:01:25 GMT

%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.

Re: Array looping through dates

Uknown_user — Fri, 19 Aug 2016 07:08:41 GMT

Thanks for help!

Jiri