How do I create a more efficient Macro to add rows to a table

jlozano1 — Fri, 07 Jul 2017 11:50:54 GMT

I have a table that includes a customer name and the difference (in months) between two dates. I need a macro that, based on the number of months in between the two dates, adds that determined amount of rows.

Basically, my table has 4 columns: Customer Name, Date 1, Date 2 and Months. Months refers to the difference in Months between Date 1 and Date 2. Again, my objective is to add rows below each customer. Number of rows to be added should depend on the number of "Months".

I tried building some code myself but ir takes forever to run. I am using SAS Enterprise Guide version 7.13

Looking for some smart suggestions on how I can create a more efficient method. Table has about 200,000 rows and last time I tried running it, it took more than 8 hours. Any thoughts?

Here's my attempt:

proc sql noprint;
select max(monotonic())
into : num_rows
from work.table_template
;
quit;

%put Number of rows is: &num_rows.;

options mprint merror mlogic symbolgen;

%macro one;

%do i = 1 %to &num_rows.;
data _null_;
set work.table_template;
if _n_ = &i.;
call symputx('Customer_Name',Customer_Name);
call symputx('Date1',Date1);
call symputx('Date2',Date2);
call symputx('Months',Months);
run;

%do j = 2 %to (&months.-1);
data _null_;
call symputx('Date1',intnx('month',&Date1.,1));
run;

proc sql;
insert into work.table_template
values ("&Customer_Name.",&Date1.,&Date2.,&Months.);
quit;
%end;
%end;

%mend one;

%one;

Re: How do I create a more efficient Macro to add rows to a table

Kurt_Bremser — Fri, 07 Jul 2017 11:59:08 GMT

You're overcomplicating things by lightyears.

Maxim 11: A macro is not needed.

Is true for close to 99.99% of cases with SAS beginners.

Consider this:

data want;
set have;
do i = 1 to months;
  output;
end;
run;

This will basically do the expand in one sweep.

Add custom calculated values in the do loop.

Re: How do I create a more efficient Macro to add rows to a table

jlozano1 — Fri, 07 Jul 2017 12:03:59 GMT

Thanks for the quick response.

Worked perfectly. took about 15 seconds to run. I'll work on the custom values.

Thanks!

topic Re: How do I create a more efficient Macro to add rows to a table in SAS Programming

How do I create a more efficient Macro to add rows to a table

Re: How do I create a more efficient Macro to add rows to a table

Re: How do I create a more efficient Macro to add rows to a table