topic Re: Using _eg_WhereParam Prompts in titles in SAS Enterprise Guide

Using _eg_WhereParam Prompts in titles

BHull — Tue, 02 May 2017 15:17:06 GMT

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

Re: Using _eg_WhereParam Prompts in titles

TomKari — Tue, 02 May 2017 15:37:00 GMT

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

Re: Using _eg_WhereParam Prompts in titles

BHull — Tue, 02 May 2017 17:53:09 GMT

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;

Re: Using _eg_WhereParam Prompts in titles

TomKari — Tue, 02 May 2017 18:23:14 GMT

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;

Re: Using _eg_WhereParam Prompts in titles

BHull — Fri, 12 May 2017 14:04:28 GMT

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;

Re: Using _eg_WhereParam Prompts in titles

TomKari — Fri, 12 May 2017 17:33:03 GMT

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;

Re: Using _eg_WhereParam Prompts in titles

BHull — Fri, 12 May 2017 17:42:28 GMT

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

Re: Using _eg_WhereParam Prompts in titles

TomKari — Fri, 12 May 2017 18:17:05 GMT

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

Re: Using _eg_WhereParam Prompts in titles

BHull — Fri, 12 May 2017 18:44:38 GMT

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;

Re: Using _eg_WhereParam Prompts in titles

TomKari — Fri, 12 May 2017 19:15:37 GMT

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;

Re: Using _eg_WhereParam Prompts in titles

BHull — Fri, 12 May 2017 20:08:28 GMT

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

Re: Using _eg_WhereParam Prompts in titles

TomKari — Fri, 12 May 2017 22:26:59 GMT

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