DATA Step, Macro, Functions and more

Two do loop variables but only one do loop

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

Two do loop variables but only one do loop

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

 

 

 

 


Accepted Solutions
Solution
‎04-05-2017 10:42 AM
Respected Advisor
Posts: 3,777

Re: Two do loop variables but only one do loop

%let year=1995;

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

View solution in original post


All Replies
Solution
‎04-05-2017 10:42 AM
Respected Advisor
Posts: 3,777

Re: Two do loop variables but only one do loop

%let year=1995;

%let year=%eval(&year+1);
Trusted Advisor
Posts: 1,128

Re: Two do loop variables but only one do loop

you want the variables to be created as c4 as c1996_prev, c1996_curr .............. for all the years to 2014
Thanks,
Jag
Frequent Contributor
Posts: 128

Re: Two do loop variables but only one do loop

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

 

 

 

Super User
Posts: 5,079

Re: Two do loop variables but only one do loop

[ Edited ]

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.

Frequent Contributor
Posts: 128

Re: Two do loop variables but only one do loop

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.

Super User
Super User
Posts: 7,392

Re: Two do loop variables but only one do loop

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.

Frequent Contributor
Posts: 128

Re: Two do loop variables but only one do loop

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 132 views
  • 0 likes
  • 5 in conversation