topic How could I use macro to simplify this code? in SAS Data Management

How could I use macro to simplify this code?

cecily — Wed, 01 Jul 2015 00:59:00 GMT

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;

Re: How could I use macro to simplify this code?

TomKari — Wed, 01 Jul 2015 13:31:00 GMT

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

Tom

Re: How could I use macro to simplify this code?

Astounding — Wed, 01 Jul 2015 13:45:01 GMT

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.

Re: How could I use macro to simplify this code?

cecily — Wed, 01 Jul 2015 14:48:29 GMT

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?

Re: How could I use macro to simplify this code?

cecily — Wed, 01 Jul 2015 15:13:13 GMT

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 ?

Re: How could I use macro to simplify this code?

Astounding — Wed, 01 Jul 2015 15:25:41 GMT

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?

Re: How could I use macro to simplify this code?

cecily — Wed, 01 Jul 2015 15:40:39 GMT

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.

Re: How could I use macro to simplify this code?

Astounding — Wed, 01 Jul 2015 17:42:11 GMT

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