Desktop productivity for business analysts and programmers

Using _eg_WhereParam Prompts in titles

Reply
Occasional Contributor
Posts: 16

Using _eg_WhereParam Prompts in titles

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!!!

 

PROC Star
Posts: 1,146

Re: Using _eg_WhereParam Prompts in titles

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

Occasional Contributor
Posts: 16

Re: Using _eg_WhereParam Prompts in titles

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;
PROC Star
Posts: 1,146

Re: Using _eg_WhereParam Prompts in titles

[ Edited ]

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;

 

Occasional Contributor
Posts: 16

Re: Using _eg_WhereParam Prompts in titles

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;

PROC Star
Posts: 1,146

Re: Using _eg_WhereParam Prompts in titles

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;

 

Occasional Contributor
Posts: 16

Re: Using _eg_WhereParam Prompts in titles

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...

PROC Star
Posts: 1,146

Re: Using _eg_WhereParam Prompts in titles

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

Occasional Contributor
Posts: 16

Re: Using _eg_WhereParam Prompts in titles

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;

PROC Star
Posts: 1,146

Re: Using _eg_WhereParam Prompts in titles

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;

 

Occasional Contributor
Posts: 16

Re: Using _eg_WhereParam Prompts in titles

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...
PROC Star
Posts: 1,146

Re: Using _eg_WhereParam Prompts in titles

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

Ask a Question
Discussion stats
  • 11 replies
  • 233 views
  • 0 likes
  • 2 in conversation