How to convert macro

[niloya]

Hi ,

Would you please let me know how to convert the codes below to macro if SC value change as 1,2,3,4,5,6 ? Thank you

Data work.want;                            

Set work.have;  

if SC=1 and u_rep =1 and ((co1*(Daily**2))+(co_dem_1*daily)+(int_1))>99.7 then

exp_val =1;

Else if SC=1 and u_rep =1 and  ((co1*(Daily**2))+(co_dem_1*daily)+(int_1))<99.7

then

exp_val=(( co_dem_1*((co1*(Daily**2))+(co_dem_1*daily)+(int_1))/100

;

if SC=2 and u_rep =1 and ((co2*(Daily**2))+(co_dem_2*daily)+(int_2))>99.7 then

exp_val =1;

Else if SC=2 and u_rep =1 and  ((co2*(Daily**2))+(co_dem_2*daily)+(int_2))<99.7

then

exp_val=(( co_dem_2 *((co2*(Daily**2))+(co_dem_2*daily)+(int_2))/100

;

if SC=3 and u_rep =1 and ((co3*(Daily**2))+(co_dem_3*daily)+(int_3))>99.7 then

exp_val =1;

Else if SC=2 and u_rep =1 and  ((co3*(Daily**2))+(co_dem_3*daily)+(int_3))<99.7

then

exp_val=(( co_dem_3 *((co3*(Daily**2))+(co_dem_3*daily)+(int_3))/100

;

[PaigeMiller]

First you need to create working code without macros and without macro variables, for at least one case (for example when a=1). Your code as written does not work, and so any macro written using this code will not work.

The structure you are using 

data.have.column1

is not valid SAS syntax and will cause errors. Thus the first step is to fix this.

[Astounding]

You could use macro language.  But it is not clearly a good choice.  Arrays would work pretty well:

data work.want;                            
set work.have;  

array co {6};
array co_cem_ {6};                           
array int_ {6};
 
if u_rep=1 and (1 <= SC <= 6) then do;
   exp_val =  (co{SC}*(Daily**2))+(co_dem_{SC}*daily)+(int_{SC});
   if exp_val >99.7 then exp_val =1;
   else exp_val = exp_val / 100;
end;

run;

You'll have to check the formulas to make sure that I got them right.  They were not entirely consistent in your original post.

Also you will need to check what should happen when the formula generates 99.7 exactly.  Your logic didn't account for that, although I did assign values for those observations.

If the number of items changes, just change "6" to the proper number (in 4 places).

[sustagens · Accepted solution]

If you really want a macro program:

%macro get_sc_val(num);
data want;
set have;

initial_val=(co&num*(Daily**2)) + (co_dem_&num*Daily) + (int_&num);
exp_val=(co_dem_&num*(co&num*(Daily**2))) + (co_dem_&num*Daily) + (int_&num);

if u_rep=1 and sc=&num then do;
	if initial_val > 99.7 then exp_val=1;		/*1 if greater than 99.7*/
	if initial_val < 99.7 exp_val=exp_val/100;	/*computed value if less than 99.7*/
	else exp_val=.;					/*null if equal to 99.7*/
end;

run;
%mend get_sc_val;

Keep in mind that the macro variable "num" resolves to a string.

Also the exact value of 99.7 is not handled. In the code above it will be assigned a null value indicated by a period. Just change this to whatever value you like.

You can initiate a run either for all values, using another macro program:

%macro run_all_nums;
	%do i=1 %to 6;
	%get_sc_val(&i);
	%end;
%mend run_all_nums;

%run_all_nums; /*execute for all nums*/

or one at a time, just change the value inside the parenthesis:

%get_sc_val(1); /*just execute for 1*/

[niloya]

You are a really good programmer. It is working. Thank you so much. I really appreciate your help

[niloya]

Just a quick question if you dont mind. i need all numbers to show into one table from 1 to 6. When i run 
the macro below, the table only show the i=6 result. Is there anything you would recommend?

%macro run_all_nums;
	%do i=1 %to 6;
	%get_sc_val(&i);
	%end;
%mend run_all_nums;

%run_all_nums;

[sustagens]

Sorry my bad, to get separate tables for each value of num, when running them all at once, add _&num to the data set name, "want".

data want_#

Adding this, you can utilise the same macro program and append all the output datasets afterwards manually.

data want;
set want_1 want_2 want_3 want_4 want_5 want6;
run;

/*or*/

data want;
set want_:;
run;

The original code replaces the table "want" every time the macro program is run, that is why you only see the last value (6).

My first suggestion doesn't look to be the most efficient way of doing it if what you require is to have them all in one dataset, even if the desired output can be achieved with the first code. You can simply go over it with a do loop, try this code below instead:

%macro program;
data want;
set have;

%do i=1 %to 6;
%let num=&i;

initial_val=(co&num*(Daily**2)) + (co_dem_&num*Daily) + (int_&num);
exp_val=(co_dem_&num*(co&num*(Daily**2))) + (co_dem_&num*Daily) + (int_&num);

if u_rep=1 and sc=&num then do;
	if initial_val > 99.7 then exp_val=1;			/*1 if greater than 99.7*/
	if initial_val < 99.7 then exp_val=exp_val/100;	/*computed value if less than 99.7*/
	else exp_val=.;									/*null if equal to 99.7*/
end;

%end;
run;
%mend;

%program;

[niloya]

@sustagens
You are the best ever. It is working beyond great. Million times thank you. You saved my life.