Understanding the %DO loop conditional %IF %THEN

[gtlightfoot]

Can anyone please explain what each element in the line in blue is doing? In particular, what is the comma in the "%then," doing and why does the %to value seem to need to match the value in "&count ne #" or else an error will result? Thanks in advance!

data purchases;
input id year order;
datalines;
123 2018 1
124 2018 1
124 2018 2
125 2018 1
125 2018 2
125 2018 3
;

%macro transpose;
proc sql;
create table trans_purchases as
select distinct
    id,
    year,
    %do count = 1 %to 3;
        case
            when order = &count.
            then 1
            else 0
        end as order&count.
    %if (&count. ne 3) %then,;
    %end;
from purchases;
quit;
%mend;
%transpose;

[Astounding · Accepted solution]

Each iteration of the %DO loop generates a CASE statement within PROC SQL.  For you to appreciate that, you will need to add this statement before running the macro (and then examine the log):

options mprint;

With multiple CASE statements, SQL syntax requires a comma separating them.  So the statement you are asking about adds a comma after the CASE statement, as long as the %DO loop is on the first or second iteration (but not after all the CASE statements have been added).  The overall idea of macro language is to generate a valid SAS program.  You have to (no exceptions here) understand what the valid SAS program looks like, in order to understand the role of macro language.

[gtlightfoot]

Thank you so much for the clear answer! This makes perfect sense.

[RW9]

Its just bad coding.  You have to remember that the macro code gets expanded by the pre-processor, before going into the base sas compiler.  So the code after preprocessor looks like this:

data purchases;
input id year order;
datalines;
123 2018 1
124 2018 1
124 2018 2
125 2018 1
125 2018 2
125 2018 3
;

proc sql;
create table trans_purchases as
select distinct
    id,
    year,
        case
            when order = 1
            then 1
            else 0
        end as order1,
        case
            when order = 2
            then 1
            else 0
        end as order2,
        case
            when order = 3
            then 1
            else 0
        end as order3
from purchases;
quit;

The if is ther to add the comma to the end of each iteration except the last one, as putting a comma after last select part will make invalid code.

I say it is bad as its far simpler to do:

data purchases;
  input id year order;
  array ord{3} 8;
  do i=1 to 3;
    ord{i}=ifn(order=i,1,0);
  end;
datalines;
123 2018 1
124 2018 1
124 2018 2
125 2018 1
125 2018 2
125 2018 3
;
run;

No need for all that messy macro code.

[gtlightfoot]

Thank you for this response! Your suggestion is definitely more elegant. I am interested to experiment with this using very large data sets to see if it will improve efficiency.

[Tom]

If you get in the habit of placing the continuation character/word for multiple line statements at the start of the line instead of the end of the line then you will not only have easier to read code but you can eliminate the need for the conditional comma.  It is much more likely that you have id or grouping variables at the start of a query than at the end so normally generated blocks of columns will all need a leading comma.

Note that boolean expressions already evaluate to 0/1 results so there is no need for complicated CASE clauses or IFN() function calls to generate boolean flags.

Also keeping the indentation of the macro code and the SAS code the macro is generating independent will make the code a little clearer without forcing the use of such long indents that there is no room on the line for the actual code.

%macro transpose;
proc sql;
  create table trans_purchases as
    select distinct
           id
         , year
%do count = 1 %to 3;
         , order = &count as order&count
%end;
    from purchases
  ;
quit;
%mend;

[gtlightfoot]

Thanks! Another good point with the boolean expressions.

[ballardw]

---

@gtlightfoot wrote:

Can anyone please explain what each element in the line in blue is doing? In particular, what is the comma in the "%then," doing and why does the %to value seem to need to match the value in "&count ne #" or else an error will result? Thanks in advance!

data purchases;
input id year order;
datalines;
123 2018 1
124 2018 1
124 2018 2
125 2018 1
125 2018 2
125 2018 3
;

%macro transpose;
proc sql;
create table trans_purchases as
select distinct
    id,
    year,
    %do count = 1 %to 3;
        case
            when order = &count.
            then 1
            else 0
        end as order&count.
    %if (&count. ne 3) %then,;
    %end;
from purchases;
quit;
%mend;
%transpose;

---

Because of the structure of Select statements requiring a comma before any item except the first  it would likely be much easier to use

%do count = 1 %to 3;
        ,case
            when order = &count.
            then 1
            else 0
        end as order&count.

  %end;

which always places the comma before without having to test and exclude a following comma