proc sql within a mcaro

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

proc sql within a mcaro

Hi,

 I have a sas code which works fine by itself, but when I try to put it into a macro it doesn't function at all, and I suspect that it might be because of the proc sql which inside the macro because I m not sure that this can be done.

Please let me know if I am doing any significant mistake and how is it possible to solve it

Here is the code that I am trying to do:

 

%macro npv(n=,cf=,fv=,r= );
data net_present_value;
%do i = 1 %to &n; output;
%end;
run;

data net_present_value;
set net_present_value;
cf=&cf;
%if i=10 %then final_value=&fv; %else final_value=0;
total=cf+final_value;
pv = total/(1+&r)**i;
run;

proc sql;
create table npv as select sum(pv) as npv from net_present_value;
quit;

%mend npv;

%npv(n=10,cf=10,fv=100,r=0.05)

 

 

Thank you!!

 


Accepted Solutions
Solution
‎11-25-2015 08:39 PM
Frequent Contributor
Posts: 128

Re: proc sql within a mcaro

A few things that I noticed.  First, in the initial data step when you're creating net_present_value, if you are trying to create a data set with just the column i and fill the values equal to 1 through &n (which would be 10 in this case), you need to remove the % signs in your do, to, and end statements.  They are not needed because they're inside of a data step and not outside.  Doing this will create a temporary data set with the variable i and 10 observations that looks like:

i
1
2
3
4
5
6
7
8
9
10

 

Second, in the second data step, when you are comparing the value of i=10, again the if, then, and else statments do not need the % signs as they are inside the data step.  This data step will then give you new variables along with the original i.  The first will be cf and equal to 10 for all 10 observations, the next will be final_value which will be 0 for observations 1-9 and 100 for observation 10, the third variable will be total and that will equal the same value of cf for observations 1-9 and then 110 for the 10th observation, and finally the variable pv will be equal to the total/(1+r)^i.  So, as your observations increase, your value of pv will be decreasing; then when it gets to the 10th observation, it will take 110/(1+0.05)^10.  The data set would look like:

i cf final_value total pv
1 10 0 10 9.52381
2 10 0 10 9.070295
3 10 0 10 8.638376
4 10 0 10 8.227025
5 10 0 10 7.835262
6 10 0 10 7.462154
7 10 0 10 7.106813
8 10 0 10 6.768394
9 10 0 10 6.446089
10 10 100 110 67.53046

 

Third, with your sql procedure, you will sum of the values of pv in the data set and you'll have one variable and one ovservation that looks like this:

npv
138.6087

 

Is this what you are trying to accomplish with the code and macro?

View solution in original post


All Replies
Contributor
Posts: 26

Re: proc sql within a mcaro

please check if the below code is what you want to do

 

%macro npv(n=,cf=,fv=,r= );
data net_present_value;
attrib i length=8;
i = 0;
%do i = 1 %to &n; i = i + 1; output;
%end;
run;
data net_present_value;
set net_present_value;
cf=&cf;
if i=10 then final_value=&fv; else final_value=0;
total=cf+final_value;
pv = total/(1+&r)**i;
run;
proc sql;
create table npv as select sum(pv) as npv from net_present_value;
quit;
%mend npv;
%npv(n=10,cf=10,fv=100,r=0.05);

Super Contributor
Posts: 413

Re: proc sql within a mcaro

Hi AbhiD,

 

Thank you for replying, but unfortunately when I did : %npv(n=10,cf=10,fv=100,r=0.05);

I didn't get anything at all...

 

The code by itself works fine so there is no need to modify it in any way, I think the problem is with the macro.

 

 

Thnka you!

Contributor
Posts: 26

Re: proc sql within a mcaro

Hi,

When you say you did not get anything at all means is there no output from the macro

 

coz when i run the macro it creates 2 datasets 

 

NOTE: There were 10 observations read from the data set WORK.NET_PRESENT_VALUE.
NOTE: The data set WORK.NET_PRESENT_VALUE has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

 

NOTE: Table WORK.NPV created, with 1 rows and 1 columns.

 

and the finale NPV has the following value

 

npv :138.60867465

 

 

The issue with your code is u are trying to mix macro code with normal variables hence its giving the error.

 

i changed the code to use normal IF statements and defaulted the value of variable 'i' in the first datastep , and it gave me the above result.

 

in case it is not working please could you provide the log of the macro that you executed.

 

Rgds,

Abhi

Super Contributor
Posts: 413

Re: proc sql within a mcaro

Its strange because I don't get any new tables and the only output in the log is the code itself, no error message whatsoever...

Contributor
Posts: 26

Re: proc sql within a mcaro

Try to compile the macro first and then run the macro call seperately.

 

Rgds,
Abhi

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: proc sql within a mcaro

Your code doesn't make sense I am afraid.  Provide test data in the form of a datastep and what you want out.  

As some tips, this code:

data net_present_value;
  %do i = 1 %to &n; output;
  %end;
run;

Will not do anything other than create and empty dataset, you have no set clause, and are not doing anything but call output with nothing to output.

Secondly the code:

%if i=10 %then final_value=&fv; %else final_value=0;

There is no variable i, if you want to use the macro variable, then it would be &i., be careful though as macro variables are always text, so there is implicit conversion there, and &i. is the incrementor from above, so why would you use that anyways, doesn't make sense.

 

Finally, also post your log output, and make sure you use:

options mlogic mprint symbolgen;

The above are debug commands so you can see what macro variables reflect, and what code is generated.  In this instance however, I don't see why you are doing this in a macro in the first place.  Post your data (as a datastep so we don't have to type it in), and what you want out.

 

 

 

 

 

 

 

 

Solution
‎11-25-2015 08:39 PM
Frequent Contributor
Posts: 128

Re: proc sql within a mcaro

A few things that I noticed.  First, in the initial data step when you're creating net_present_value, if you are trying to create a data set with just the column i and fill the values equal to 1 through &n (which would be 10 in this case), you need to remove the % signs in your do, to, and end statements.  They are not needed because they're inside of a data step and not outside.  Doing this will create a temporary data set with the variable i and 10 observations that looks like:

i
1
2
3
4
5
6
7
8
9
10

 

Second, in the second data step, when you are comparing the value of i=10, again the if, then, and else statments do not need the % signs as they are inside the data step.  This data step will then give you new variables along with the original i.  The first will be cf and equal to 10 for all 10 observations, the next will be final_value which will be 0 for observations 1-9 and 100 for observation 10, the third variable will be total and that will equal the same value of cf for observations 1-9 and then 110 for the 10th observation, and finally the variable pv will be equal to the total/(1+r)^i.  So, as your observations increase, your value of pv will be decreasing; then when it gets to the 10th observation, it will take 110/(1+0.05)^10.  The data set would look like:

i cf final_value total pv
1 10 0 10 9.52381
2 10 0 10 9.070295
3 10 0 10 8.638376
4 10 0 10 8.227025
5 10 0 10 7.835262
6 10 0 10 7.462154
7 10 0 10 7.106813
8 10 0 10 6.768394
9 10 0 10 6.446089
10 10 100 110 67.53046

 

Third, with your sql procedure, you will sum of the values of pv in the data set and you'll have one variable and one ovservation that looks like this:

npv
138.6087

 

Is this what you are trying to accomplish with the code and macro?

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 530 views
  • 2 likes
  • 4 in conversation