topic Re: How to convert macro in SAS Programming

How to convert macro

niloya — Mon, 04 Nov 2019 15:21:57 GMT

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

;

Re: How to convert macro

PaigeMiller — Mon, 04 Nov 2019 15:08:05 GMT

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.

Re: How to convert macro

Astounding — Mon, 04 Nov 2019 18:15:33 GMT

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).

Re: How to convert macro

sustagens — Tue, 05 Nov 2019 09:19:21 GMT

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*/

Re: How to convert macro

niloya — Tue, 05 Nov 2019 14:54:22 GMT

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

Re: How to convert macro

niloya — Tue, 05 Nov 2019 20:11:46 GMT


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;

Re: How to convert macro

sustagens — Wed, 06 Nov 2019 03:37:44 GMT

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_&num;

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;

Re: How to convert macro

niloya — Wed, 06 Nov 2019 14:01:10 GMT

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