BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MeganE
Pyrite | Level 9

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19
%let year=1995;

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

View solution in original post

7 REPLIES 7
data_null__
Jade | Level 19
%let year=1995;

%let year=%eval(&year+1);
Jagadishkatam
Amethyst | Level 16
you want the variables to be created as c4 as c1996_prev, c1996_curr .............. for all the years to 2014
Thanks,
Jag
MeganE
Pyrite | Level 9

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

 

 

 

Astounding
PROC Star

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
Pyrite | Level 9

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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