SAS: using first. last. in %if in %macro

1
votes

I am trying to build bins for numeric variables. Everything worked fine when I haven't used %macro therefore I believe it's something macro specific. Basically this part of code:

%if first.&rank_column. = 1 %then %do;

doesn't work as I anticipated. Below I simplified to just one iteration and one variable but my actual code have many more - however the problem is the same.

Full code:

/* Build binns */
%let var_list = MSRP Invoice;

%macro group_var_in_bins(source_table);

data WITH_BINNS;
    set &source_table.;
run;

%let loop_count = %sysfunc(countw(&var_list));

%do i=1 %to &loop_count.;

    * list of variables to use;
    %let variable = %scan(&var_list, &i);
    %let rank_column = &variable._bins_rank;

    proc rank data=WITH_BINNS out=WITH_BINNS groups=10 ties=low;
        var &variable.;
        ranks &rank_column.;
    run;

    * sort the table by this variable;
    proc sort data=WITH_BINNS out=WITH_BINNS;
        by &variable.;
    run;

    * Build start and end observation of particular bin;
    data WITH_BINNS;

        set WITH_BINNS;

        by &rank_column.;

        *this is just to check if first.&rank_column works;
        first_&rank_column. = first.&rank_column.;
        last_&rank_column. = last.&rank_column.;

        %if first.&rank_column. = 1 %then %do;
            /* here %if first.&rank_column. %then %do erros so something is wrong with argument statement*/
            Start_bin = &variable.;
        %end;
        %else %do;
            Start_bin = .;
        %end;

        %if last.&rank_column. = 1 %then %do;
            End_bin = &variable.;
        %end;
        %else %do;
            End_bin = .;
        %end;

    run;
%end;

* some more code which amends WITH_BINNS table;

%mend group_var_in_bins;

%group_var_in_bins(sashelp.cars);

The result is as: enter image description here

so the loop doesn't recognize argument in %if part.

Thanks for help!!

[Edit]:

To clarify, steps I want to do are:

  1. for variable in list &var_list.

  2. build a rank for it

  3. sort by that variable

  4. using data step by: group by rank

  5. find the value of this variable which corresponds to beggining of group using first. and end end of group using last. leave the rest empty

  6. some next steps...

So basically I want to create begging and end of rank interval.

I example from picture; the first row has first. = 1 therefore Start_bin should e $10.280 and End_bin should be empty. The next row should be empty because both first. and last. are 0.

2
sassas-macro
You probably want to use if, not %if. – Hong Ooi
You use macro logic to conditionally generate code. Why do you think you need to conditionally generate code? If you just want to execute different statements based on a condition then use a if statement. That will be able to see data step variables like the first. variables generated when you use a by statement. – Tom
If you want help solving your real problem then provide a description of what you are trying to do and give some example input data and the desired results. – Tom
hi @Tom I've used sashelp.cars so you could replicate my example. and added the steps I am doing and the result I want to achieve. I hope this clarifies. – Mateusz Konopelski
%if is for macro conditional logic to control what code your macro generates. You still use if and where for data conditional operations. Macros just generate SAS code, so they need to generate valid SAS code. – Tom

2 Answers

1
votes

So your basic problem is you are using macro logic where you should be using normal logic.

%if first.&rank_column. = 1 %then %do;

Will never be true, even if rank_column is empty because the string first. can never equal the string 1.

But if you code it using SAS code instead of macro code

if first.&rank_column. = 1 then do;

Then it will be true when you are on the first observation in that particular value of the variable named by the value of the macro variable rank_column.

You probably have bigger problems with your overall logic because you are overwriting the same variable names start_bin and end_bin in the same dataset. So only the values for the bins generated by the last variable your list will be available after the macro has finished.

1
votes

If you're ultimately looking for the bin boundaries, with groups=10, wouldn't that be equivalent to finding the percentiles via proc summary or proc means? The benefit of doing via percentiles method also means you can handle multiple variables at once. I didn't set any tie options here, but that can also be set I believe. 

ods select none; /*do not display output - faster processing*/
proc means data=sashelp.cars /*input data set*/
    stackods  /*stack ods to have the table appear with statistics across the top*/
    N NMISS Min P10 P20 P30 P40 P50 P60 P70 P80 P90 Max /*stats to show*/;
var mpg_city mpg_highway invoice msrp; /*variables included in analysis*/
ods output summary = want; /*capture output into a data set*/
run;
ods select all; /*reset output options*/