DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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;


Accepted Solutions
Solution
‎07-07-2017 08:24 AM
Super User
Posts: 7,853

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

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.

 

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

View solution in original post


All Replies
Solution
‎07-07-2017 08:24 AM
Super User
Posts: 7,853

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

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 2

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

Posted in reply to KurtBremser

Thanks for the quick response. 

 

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

 

Thanks!

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 167 views
  • 1 like
  • 2 in conversation