topic Re: Looping Dates as Macro Variables from a Table in New SAS User

Looping Dates as Macro Variables from a Table

Anirudh9 — Mon, 17 Jun 2019 22:04:22 GMT

I have a table available_dates which has row of date values, like this:-

Table: available_dates

date_value

05MAR19
06MAR19
07MAR19
08MAR19

I have a huge 5000+ lines of code that uses a macro_variable called &start_date. This macro-variable is used multiple times throughout the code.

Now, I want that entire code to run n times, where n is number of rows of date in table available_dates (n = 4 in this case)

For every iteration, I want &start_date to be a specific date from the table available_dates

Essentially, the code will first have &start_date = 05MAR19, then run all 5000 lines, then &start_date = 06MAR19, then run all 5000 lines, then &start_date = 07MAR19, then run all 5000 lines and so on.

How do I this looping? I see a lot of posts to loop over strings, but this is a date value, so its getting a little tricky.

Re: Looping Dates as Macro Variables from a Table

ehmsoleil — Mon, 17 Jun 2019 22:41:12 GMT

I would capture the number of dates into a macro then use a loop to retrieve the dates.

The date needs to be changed to a number while in the macro.

This code will create a macro called start_date that is the date from your available_dates set.

proc sql noprint;

select count(*) into: ct

from available_dates;

quit;

%macro dates;

%do i = 1 %to &ct.;

data _null_;

set available_dates;

format date_value 12.;

if &i. = _n_ then call symput('start_date ',date_value);

run;

%put &start_date .;

..... lines of code.......;

%end;

%mend;

%dates;

Re: Looping Dates as Macro Variables from a Table

Patrick — Tue, 18 Jun 2019 00:56:28 GMT

@Anirudh9

Assuming your big program is stored in an external .sas file below code how you can call this program multiple times, each time setting macro variable &start_date to a new value. Below approach doesn't require your big program being wrapped into a macro.

/* create sample program */
filename code temp;
data _null_;
  file code;
  put
    'data _null_;'/
    '  put "Start_Date is: &start_date";'/
    '  stop;'/
    'run;'/
    ;
  stop;
run;

/* below code pretty much as you could use it */

/* 
  filename code '<full path & name of your .sas program>';
*/
data date_value;
  input start_date :$9.;
  datalines;
05MAR19
06MAR19
07MAR19
08MAR19
;

data _null_;
  set date_value;
  length cmd $200;
  cmd='data _null_;'||cats('call symputx("start_date",',"'",start_date,"'",');stop;run;');
  cmd=cats(cmd,'%include code / source2;');
  call execute(cmd);
run;

Re: Looping Dates as Macro Variables from a Table

heffo — Tue, 18 Jun 2019 00:53:25 GMT

A slight variation of other persons code:

data available_dates;
	input date_value date9.;
	format date_value date9.;
datalines;
05MAR19
06MAR19
07MAR19
08MAR19
;;;
run;
 
%macro bigCode(start_date);
	*Here you put your 5000 lines of code.;
	%put Do fun things with the startdate "&start_date";
%mend bigCode;

%macro dates (ds_name, var_name);
	proc sql noprint; 
		select count(*) into: ct
		from &ds_name;
	quit; 

	%do i = 1 %to &ct.;
		data _null_;
			set available_dates (firstobs=&i obs=&i); *Only read one line at each pass.;
			call symputx('start_date',&var_name); 
		run;
		**********************************************;
		* Call your big macro from here with &start_date as a parameter.;
		**********************************************;
		%bigCode (&start_date)
	%end;
%mend;

%dates (available_dates, date_value);

Re: Looping Dates as Macro Variables from a Table

Tom — Tue, 18 Jun 2019 01:10:45 GMT

Personally I would convert the program into a macro. Could be as simple as

%macro run_5000(start_date);
%include 'program_with_5000_lines.sas';
%mend run_5000;

Then call the macro once for each observation in the dataset.

data _null_;
  set available_dates ;
  call execute(cats('%nrstr(%run_5000)(',put(date_value,date9.),')');
run;

Re: Looping Dates as Macro Variables from a Table

Kurt_Bremser — Tue, 18 Jun 2019 07:52:36 GMT

Strongly second @Tom 's suggestion; when data is already present in a dataset, there is no need to take the detour through a list of macro variables (which is not a good solution as such, anyway). call execute() is the way to go.

Re: Looping Dates as Macro Variables from a Table

ehmsoleil — Tue, 18 Jun 2019 12:01:19 GMT

Hey, nerd, it is a FINE “solution as such.” Especially when someone is learning macros and needs the flexibility.

Re: Looping Dates as Macro Variables from a Table

Kurt_Bremser — Tue, 18 Jun 2019 14:39:01 GMT

This alone

data _null_;
set available_dates;
format date_value 12.;
if &i. = _n_ then call symput('start_date ',date_value);
run;

shows that you should invest MUCH more time learning to work with Base SAS before wasting time with inefficient abuse of the macro preprocessor.

The format statement is not necessary (call symput() will alway store the raw value, unless you force a format by using the put() function), and the data step will read the whole dataset in every iteration of the macro loop. Use the point= option to retrieve a single observation most efficiently.

But if you want to ignore the advice from a combined ~50 years of SAS experience, that's up to you.

Re: Looping Dates as Macro Variables from a Table

ehmsoleil — Tue, 18 Jun 2019 20:12:00 GMT

I'm not 'ignoring' Tom's solution;it is beautiful and sophisticated. I'm addressing your rudeness about my suggestion.

You don't have to be so arrogant or dismissive; there are many ways to skin a cat. If someone is just learning macros/looping he or she may want to run a less sophisticated and advanced piece of code. That's all I'm saying.

Re: Looping Dates as Macro Variables from a Table

Kurt_Bremser — Tue, 18 Jun 2019 20:22:36 GMT

Quote:

"Hey, nerd,"

I rest my case regarding rudeness.