Macro : Repeative Case statement into a Do Loop

[Uknown_user]

Hello,

I tried to make a macro with one variable where one can define quantile (e.g. flag data with appropriate mark - for example: in case it belongs to the lowest decile as Q10, etc.). The script does not store all values of i, it goes from 1 to the defined number N but then only stores the last result (i.e. N), any suggestions how to keep all values of i and thus gain all cases?:

%MACRO M(N);

DATA _NULL_;

%DO i=1 %TO &N.;

PROC SQL;

CREATE TABLE G AS

SELECT VAR1,

VAR2,

CASE WHEN VAR2 <= (1-(&i-1)/10) * MAX(VAR2) AND VAR2> (1-&i/10) * MAX(VAR2) THEN "Q&i" END AS FLAG

FROM B;

QUIT;

%END;

%MEND M;

%M(10)

Thanks for any suggestions.

Jiri

[Astounding]

Many ideas, but just one suggestion to start.  Get the program to work by hard-coding everything, without using macro language.  Once it is working, we can look at converting it into a macro.

Unless you do that, you will have to debug both SAS language errors and macro language errors at the same time ... a much more difficult task.

[Uknown_user]

It appears like I will indeed have to do that. Thanks

[Reeza]

Use a data step and array - a single data step. 

Macros will just make this complicated. 

[Reeza]

And proc rank wouldn't work either? 

If you want indicators for deciles you'll need to transpose the final table but again it's fully dynamic without macros.