How could I use macro to simplify this code?

[cecily]

proc sql;

alter table _118

   add problem_day1 num add problem_day2 num

   add problem_day3 num add problem_day4 num

   add problem_day5 num add problem_day6 num

   add problem_day7 num add problem_day8 num

   add problem_day9 num add problem_day10 num

   add problem_day11 num add problem_day12 num

   add problem_day13 num add problem_day14 num

   add problem_day15 num add problem_day16 num

   add problem_day17 num add problem_day18 num

   add problem_day19 num add problem_day20 num

   add problem_day21 num add problem_day22 num

   add problem_day23 num add problem_day24 num

   add problem_day25 num add problem_day26 num

   add problem_day27 num add problem_day28 num

   add problem_day29 num add problem_day30 num;

update _118

     set problem_day1=

      case when day=29 then distinct_problem

      else 0

  end;

update _118

  set problem_day2=

      case when day=28 then distinct_problem

     else 0

      end;

update _118

  set problem_day3=

      case when day=27 then distinct_problem

       else 0

  end;

quit;

I have tried to use the macro. But there is an problem that I can not convert the macro variable to numeric  in the sql.

%macro pao;

proc sql;

%DO i=1 %TO 30；

alter table _125

   add video_day&i num;

%END;

%DO k=1 %TO 3;

%let j=%eval(30-&k);

update _125

     set video_day&k=

      case when day=&j then distinct_video

      else 0

  end;

%END;

alter table _125

    drop date ,datelast ,day,distinct_video,date_problem,distinct_problem;

quit;

%mend;

%pao;

[TomKari]

Macro variables are always character, but your code seems to use the %eval correctly. What are the symptoms of your problems?

Tom

[cecily]

in the when clause, sas could not execute the when clause because  &j is not numeric.

Here, I would like to use the Do loop in the when clause. Because, Each time  ' video_day' is  changed, the when clause also need to be changed.

Could you help me ?

[Astounding]

It's a little difficult to decipher, since your macro doesn't exactly match your program.  For example, are you trying to alter table _125 or table _118?

At any rate, this section needs to change in two ways:

%DO i=1 %TO 30；

alter table _125

   add video_day&i num;

%END;

It should become:

alter table _125

%DO i=1 %TO 30；

   add video_day&i num

%END;

That means moving the ALTER statement out of the loop, and removing the interior semicolon.

After that, we'll have to see what problems remain.

Good luck.

[cecily]

oh.I see. I will change the code and make it better. Actually the main problem is

update _125

     set video_day&k=

      case when day=&j then distinct_video

      else 0

Here, in the when clause, sas could not execute the when clause because  &j is not numeric.

How could I solve this problem?

[Astounding · Accepted solution]

There's nothing wrong with &J and &K, at least not in the macro code that you posted.

There are other mismatches between your code, and your macro.  For example, is the field name DISTINCT_VIDEO or should it be DISTINCT_PROBLEM?

It's too much work to imagine what you might be trying to accomplish.  Why not post an updated version of the desired code you are trying to replace, an updated version of what your macro looks like, and the error message you are getting?

[cecily]

There is something wrong.

In the table table _125,  day is numeric. But here   ' when  day=&j    '  I let the numeric variable equals to a character variable . So There is a syntax error  in the code.

[Astounding]

Descriptions are not enough.  You willl need to post both your macro, and the error message.