BookmarkSubscribeRSS Feed
BHull
Obsidian | Level 7

Good day,

I've created a stored process with EG 7.12 which allows multiple selections with a prompt using _eg_WhereParam

 

Works great.

 

Now I want to put the selection(s) chosen into a title for the report.

 

_eg_WhereParam creates a new version of the prompt for each value, for example for the prompt for "Period" I get.

 

PERIOD=1

PERIOD0=2

PERIOD1=1

PERIOD2=4

PERIOD_COUNT=2

 

How do I code/build a title statement without specifying each "version" of the prompt.

 

Here's what I have now....

"title period &period1 &period2 Year &year;"

But there can be any number of selections...

 

Ideas please....and thanks in advance!!!

 

11 REPLIES 11
TomKari
Onyx | Level 15

Assuming that PERIOD_COUNT is the number of returned values for the prompt, something like this (untested):

 

Create a macro for your code, and within it:

 

title period %do _i = 1 %to &PERIOD_COUNT; &&period&_i. %end; Year &year;

 

Tom

BHull
Obsidian | Level 7
Tom,
Thank you for your advice, but I'm still struggling.
I'm not proficient with macros, but here's what I tried...with no results.
Appreciate your help!

292 +%macro title1;
293 +title period %do _i = 1 %to &PERIOD_COUNT; &&period&_i. %end; Year &year;
294 +%smend title1;
295 +run;
296 +
297 +proc print;
298 +Title &title1;
299 +run;
TomKari
Onyx | Level 15

Yes, macros are quite something to get your head around at first!

 

You've absolutely got the right idea. Here's what worked for me: everything before the %macro statement is just setting up data that should already be there in your program, but I've left it there to give you something to practice with.

 

Once you're happy with that, you should just need the code from the %macro statement to the end.

 

Give it a try,

   Tom

 

%let PERIOD_COUNT = 2;

%let PERIOD1 = 9;

%let PERIOD2 = 6;

%let year = 2014;

data work.class;

set sashelp.class;

run; /* To load a dataset in the _LAST_ position */

%macro title1;

title period %do _i = 1 %to &PERIOD_COUNT; &&period&_i.%end; Year &year;

proc print;

run;

%mend;

%title1;

 

BHull
Obsidian | Level 7
Tom,
Your last suggestion worked great...thanks...
I'm trying to expand on that logic and getting nowhere.
I'm simply trying to iterate through and execute a macro N number of times.
Here are two attempts...one with macro the other with data step. both fail to resolve _i
I don't think I can use the CALL function inside the macro...that's why I tried data step...
I'm stuck...any suggestions...and thank you!
%macro charts;
%do _i = 1 %to &PERIOD_COUNT;
&let year=&year&_i.;
call symputx('YEARCODE','and (put(t1.SALES_YEAR,4.)) = &&YEAR&i');
call symputx('yeartitle',&&year&i);
%let YEARCODE='and (put(t1.SALES_YEAR,4.)) = &&YEAR');
%let yeartitle=&year;
%let market=&marketx;
call execute('%chartit');
%end;
run;
%mend;
%charts;

data _null_;
do _i = 1 to &PERIOD_COUNT;
%let year=&year&_i.;
call symputx('YEARCODE','and (put(t1.SALES_YEAR,4.)) = &&YEAR');
call symputx('yeartitle',&&year);
%let yeartitle=&&year);
call execute('%chartit');
end;
run;

TomKari
Onyx | Level 15

Yes, you're tied up in a couple of common macro problems. It's hard to see exactly where you want to get, so let's start with some of the easier problems.

 

In your first program:

 

1. I'm assuming you've set PERIOD_COUNT previously. I've added a line to set it to 5.

2. You're using the macro variable &year before you set it, which won't work. I've added a line to set it to 2017.

3. Your code has &let. You need to use %let.

4. Once you've made these changes, your

%let year=&year&_i.;

statement is going to keep extending your value.

5. If you're using macro references that you want to resolve, you need to use double quotes (") instead of single (').

 

Here's a program that will show you what's happening. The %put statement is absolutely wonderful for diagnosing these problems!

 

Give this a spin, and work at it a bit more.

 

Tom

 

%let PERIOD_COUNT=5;

%let year=2017;

%macro charts;

%do _i = 1 %to &PERIOD_COUNT;

%let year=&year&_i.;

%put &=PERIOD_COUNT &=_i &=year;

%end;

run;

%mend;

%charts;

 

BHull
Obsidian | Level 7
Tom,
Thank you for your patience...
I finally realized my problem...
I'm not trying to replace the VALUE in the macro but to rename it...
I have an original program which uses the &YEAR throughout...but only allows for 1 year.
This mod is to allow multiple year selection and execute the code for each year separately.
&YEAR1=2016, &YEAR2=2017...
So what I want is to replace &YEAR with &YEAR1, &YEAR2....until the PERIOD_COUNT is reached...
Any ideas on this???
Thanks...

TomKari
Onyx | Level 15

Okay, so here's another snippet to move you along.

 

Note how to refer to macro variables that are made up of other macro variables, like in the second %put statement.

 

%let PERIOD_COUNT=5;

%let year=2015;

%macro charts;

%do _i = 1 %to &PERIOD_COUNT;

%let year&_i. = %eval(&year+&_i);

%put &=PERIOD_COUNT &=_i &=year;

%put &&year&_i;

%put &year1 &year2 &year3 &year4 &year5;

%end;

%mend;

%charts;

 

And this might be what you're after, but I'm not positive.

 

%let PERIOD_COUNT=5;

%let year=2015;

%macro charts;

%do _i = 1 %to &PERIOD_COUNT;

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

%put &=PERIOD_COUNT &=_i &=year;

%end;

%mend;

%charts;

 

Tom

BHull
Obsidian | Level 7
Tom,
I do appreciate your patience...but this is not what I'm after...
First of all data being passed into &year is character...I think I can fix that...
Here's an idea of what I'm trying to do...
Multiple years are passed in by stored process prompts then each processed separately...
Year1='2015'
Year2='2016'
Year3='2017'
Data test;
Set work.yeardata;
If &year=year then list;
Else delete;

I want to replace in the code the variable name &year with &year1, then loop back through and process &year2, and &year3...etc...;
So it would look like this in the log...
Data test;
Set work.yeardata;
If &year1=year then list;
Else delete;
Data test;
Set work.yeardata;
If &year2=year then list;
Else delete;
Data test;
Set work.yeardata;
If &year3=year then list;
Else delete;

TomKari
Onyx | Level 15

Okay, I think I see where you're going.

 

Give this a try...the first data step, and the five %let statements for year1 to year5, are just setup to emulate your situation.

 

data have;

length year $4;

input year number;

cards;

2015 123

2016 456

2017 789

2018 987

2019 684

run;

 

%let year1=2015;

%let year2=2016;

%let year3=2017;

%let year4=2018;

%let year5=2019;

 

%let PERIOD_COUNT=5;

 

%macro charts;

%do _i = 1 %to &PERIOD_COUNT;

data want;

set have;

if "&&year&_i." = year then output;

run;

proc print data=want;

run;

%end;

%mend;

%charts;

 

BHull
Obsidian | Level 7
Code work perfectly when run alone...you nailed what I needed...
However when I try it with my huge amount of code in the macro %chartit...I get nothing...
Thank you for your help today...have a great weekend...
TomKari
Onyx | Level 15

Excellent! Glad to hear there's some progress.

 

What I suggest is that you create a different chartit macro, and just reflect back the information that's being passed in. That might give you some insight into what's not working right.

 

Tom

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 11 replies
  • 2097 views
  • 0 likes
  • 2 in conversation