Two do loop variables but only one do loop

[MeganE]

Hi,

I'm trying to run a macro in the middle of a proc sql step, and this works just fine:

proc sql;

blah blah blah

%macro c;

%do i=4 %to 22;

     a._c&i._ as c&i._prev,

     b._c&i._ as c&i._curr,

     ((b._c&i._-a._c&i._)/a._c&i._)*100 as c&i._percdiff format 5.2,

     case when calculated c&year._percdiff ne .

                and (calculated c&year._percdiff  < -2

                or 2 < calculated c&year._percdiff) then 1 else 0 end as flag_c&i.,

%end;

%mend;

%c

rest of proc sql;

quit;

But i want to add a second variable without adding a second do loop.  I don't want it to cycle twice, just the once.  But when i do this, it skips the macro altogether.

%macro c;

%do i=4 %to 22

year=1996 %to 2014;

     a._c&i._ as c&year._prev,

     b._c&i._ as c&year._curr,

     ((b._c&i._-a._c&i._)/a._c&i._)*100 as c&year._percdiff format 5.2,

     case when calculated c&year._percdiff ne .

                and (calculated c&year._percdiff  < -2

                or 2 < calculated c&year._percdiff) then 1 else 0 end as flag_c&year.,

%end;

%mend;

%c

And i can't seem to find the right search words for a google search on this.  I don't want a nested do loop. I only want the one.  I just want TWO variables in it.

Thanks
Megan

[data_null__ · Accepted solution]

%let year=1995;

%let year=%eval(&year+1);

[Jagadishkatam]

you want the variables to be created as c4 as c1996_prev, c1996_curr .............. for all the years to 2014

[MeganE]

The variables currently exist as c4 through c22.  I want to rename them to their respective years which is c1996 through c2014.

[Astounding]

Megan,

I have to ask for forgiveness up front on this one.  I just can't help myself.

Why wouldn't you want to use nested %DO loops?  On the surface, it looks like it's exactly the right tool for the job.  It's like me saying that I want to cook a pot roast, but I don't want to use a stove.  I only want to use ice cubes.  But it doesn't seem to be working.

If this is some sort of programming contest where nested %DO loops are ruled out, you could probably get away with a SAS data set and CALL EXECUTE.  But I think we need to know the conditions of contest here.

Or perhaps we're looking at something like this:

%let year = %eval(&i + 1992);

That could easily be the solution you're searching for.

[MeganE]

Because i only want the code to process once for each of the 4 through 22 values. If i nest a year do loop in that from 1996 to 2014, then it'll process 1996 to 2014 for c4, and again for c5, and again for c6.  And that doesn't work.  And it's now running 300+ times.  Every nested do loop is fully run for each cycle of the outer do loop.

[RW9]

You are really creating so much work for yourself doing it this way.  A simple change to the data model would make you life so much easier.  Go from transposed, which you currently have (i.e. across the table) to long (down the page), and then you can create simple datasteps to do the processing, on any amount of values (currently your hardcoding in 22, which might change), and if necessary transpose up for reporting out.  What you are doing is the "Excel" way of thinking.

[MeganE]

The dataset is created by proc report output that i have no control over.

As it stands, i've solved my problem.  I'm good, thanks.