topic Re: Need help with macro program in SAS Procedures

Need help with macro program

Echo77 — Sun, 20 Apr 2014 15:53:10 GMT

Column date to column y5 are what I have.

I need to take the values during different periods of time from different columns. The column z is what I try to generate.

date	y1	y2	y3	y4	y5	z
1	2	.	.	.	.	2
2	4	.	.	.	.	4
13	.	2	.	.	.	2
15	.	3	.	.	.	3
24	.	.	9	.	.	9
26	.	.	10	.	.	10
31	.	.	.	5	.	5
33	.	.	.	7	.	7
47	.	.	.	.	7	7
49	.	.	.	.	5	5

I already wrote a simple program as follows and but it does not give me what I wanted.

data test;

set test;

array cutoff(5) _temporary_(10 20 30 40 50);

do j=1 to 5;

do while (date<=cutoff(j));

set test;

array y(*) y1-y5;

current=y(j);

output;

end;

run;

This is what I have:

date	y1	y2	y3	y4	y5	z
1	2	.	.	.	.	2
2	4	.	.	.	.	4
13	.	2	.	.	.	.
15	.	3	.	.	.	3
24	.	.	9	.	.	.
26	.	.	10	.	.	10
31	.	.	.	5	.	.
33	.	.	.	7	.	7
47	.	.	.	.	7	.
49	.	.	.	.	5	5

Since I have a number of such datasets to process, I try to change it to a macro:

%macro process;

data test

set test;

array cutoff(5) _temporary_(10 20 30 40 50);

%do j=1 %to 36;

%do %while (date1<=cutoff(j));

array y(*) y1-y36;

current=y(j);

output;

%end;

run;

%end;

/*keep the current value*/

%mend process;

%process

However, when I execute the macro, I got error messages:

ERROR: Required operator not found in expression: date1<=cutoff(j)

ERROR: The condition in the %DO %WHILE loop, date1<=cutoff(j), yielded an invalid or missing

value, . The macro will stop executing.

ERROR: The macro PROCESS will stop executing.

Can anybody help me with this? Thanks!

Re: Need help with macro program

Tom — Sun, 20 Apr 2014 16:33:28 GMT

Does not look like a problem that would be helped by using macro programming.

If you your data really only has one non-missing value per row the COALESCE() function is all you need.

data want ;

set have ;

newz = coalesce(of y1-y5);

run;

Re: Need help with macro program

Kurt_Bremser — Sun, 20 Apr 2014 18:16:56 GMT

Aside from what Tom said, you tried to use data step variables in the macro %while statement. During macro execution, these do not yet exist. Keep in mind that macro processing occurs BEFORE the data step is even compiled.

Re: Need help with macro program

Echo77 — Sun, 20 Apr 2014 18:33:50 GMT

The example I showed is a simplified version. Most of the times I have more than two non-missing values in each row. When that happens, I need to choose a value according to the date. You actually helped me write the data step program when I posted the question last time:) Thanks very much!!

Re: Need help with macro program

Echo77 — Sun, 20 Apr 2014 18:39:43 GMT

Thanks a lot! Then what's the correct way? Would it help if I take out the % signs in the do while loop?

Re: Need help with macro program

Kurt_Bremser — Sun, 20 Apr 2014 18:59:57 GMT

I have some problems making sense of your code.

- j iterates 1 to 36, but array cutoff() has only 5 elements?

- where does date1 come from?

Aside from that, you may be able to solve your problem without macro processing.

Re: Need help with macro program

Tom — Sun, 20 Apr 2014 20:23:46 GMT

Here is a way to calculate the index using a FORMAT.

proc format ;

value idx low-10=1 10-20=2 20-30=3 30-40=4 40-50=5 ;

run;

data want ;

set have ;

array y(*) y1-y5;

j= input(put(date,idx.),5.);

if 1 <= j <= dim(y) then current=y(j);

run;

Since I have a number of such datasets to process

What is it that varies between the datasets? If nothing varies then just pass the dataset names to the macro.

Is it the list of date ranges that change? You could generate the format above with a macro loop.

%macro idxgen(list);

%local low high i n ;

%let n=%sysfunc(countw(&list));

%let low=low ;

proc format ;

value idx

%do i=1 %to &n ;

%let high=%scan(&list,&i) ;

&low - &high = &i

%let low=&high;

%end;

&low - high = %eval(&n+1);

;

run;

%mend idxgen ;

Re: Need help with macro program

Echo77 — Mon, 21 Apr 2014 03:58:53 GMT

Sorry, I made some mistakes here. The iteration is 5, and the variable should be date. Thanks for your time!

Re: Need help with macro program

Echo77 — Mon, 21 Apr 2014 04:02:57 GMT

This is exactly what I need!! I've been stuck here for all day~Thanks so much! I just have one more confusion: when we write macros, sometimes we need to put %do and %end. But sometimes, we need only do-end without the %sign. What's the difference here?

Re: Need help with macro program

Kurt_Bremser — Mon, 21 Apr 2014 07:38:06 GMT

do - end, written without "%", is just text that the macro processor will hand over to the main SAS interpreter. To the macro processor, this is only "garble-garble-garble". %do and %end are macro statements that the macro processor actually "understands" and acts upon.

Re: Need help with macro program

Echo77 — Tue, 22 Apr 2014 02:24:51 GMT

I see~ Thank you very much!!