DATA Step, Macro, Functions and more

Optional Criteria

Reply
Contributor
Posts: 30

Optional Criteria

I have two queries that are virtually the same. Both have a main criteria of "Payer" Where that is established by a macro

%window info

     #5 @5      'Please enter payer:'

     #5 @26    payer 5 attr+underline;

%display info;

Then it is used in a Proc SQL where clause with Payer=%unquote(%str(%'&payer%'))

However, they both have a second, static criteria. One has a criteria of Pay_Type= 'CC' and the other has a criteria of Late='0'

So currently, if I want to find the payer based on how they pay, I run the first one. But if I want to find the payer based on not being late, I run the second.

Is there a way to combine them, and make it so that I can chose which of the static criteria is used when the program is run?

Regular Contributor
Posts: 244

Re: Optional Criteria

First off, in EG it's probably easier to use a prompt, if you are running ... 4.2? or newer (not sure which version is required).  
Then you can do a lot of things, such as offer options to the user and let them pick between them. 
Second, either way you should be able to ask for the entire criteria (not just the right side) in a macro variable. 
 If I were using prompt manager, I would make a prompt for this that gave two options of text: 
1: payer='CC' 
2: late=0 

Save that to a macro variable, say "Criteria".  Then:
proc sql; 
select blah from blah 
where blah and &criteria.; 
quit; 

(perhaps some unquoting, if needed).  That should work either with your %display method or with a prompt.
Contributor
Posts: 30

Re: Optional Criteria

We don't use enterprise guide. So any solution needs to be able to be coded. And you seem to have confused a few of the variables.

The %display method is a simple macro that opens a prompt window to allow you to enter a payer code upto 5 characters long, which creates the &payer. macro variable that is used later to look up all products for that payer. Then there are two static criteria, either I'm looking for all products of the payer that used a specific Pay Type, or I'm looking for all products of a specific payer that is not late.

But I think I see where you were going with it. Store the the entire criteria as a variable. So if late='0' became the value for &criteria. I could run it

%window info

     #5 @5      'Please enter payer:'

     #5 @26    payer 5 attr+underline;

%display info;

Proc SQL;

create table PayerProductReport as

Select     Warehouse.Product_Sold.Product,

              Warehouse.Pruduct_Sold.Payer

From       Warehouse.Product_Sold

Where     Warehouse.Product_Sold.Payer=%unquote(%str(%'&payer%'))

     and    Warehouse.Product_Sold.&Criteria.;

Quit;

Then SAS would view the Warehouse.Product_Sold.&Criteria. to be Warehouse.Product_Sold.Late='0' and run it. While if the &Criteria. variable was set to Pay_Type='CC' then SAS would read it as Warehouse.Product_Sold.Pay_Type='CC'

Disclaimer: This is a simplified code with fictionalized names to help understand the concept. It has not been tested, and I may have typed a syntax error in there somewhere.

However I wouldn't want to retype the criteria everytime (It'd be easier to stick with two different programs in that case.) so I can't use the same type of prompt window already used for the &payer. variable.

So how would I code a prompt window to select which value to assign the criteria value without needing to type it?

Regular Contributor
Posts: 244

Re: Optional Criteria

You posted in the Enterprise Guide community, hence my assumption. You could give two options in your prompt and type 1 for first option 2 for second, then translate that using macro %if s to the proper criteria.  Not as flexible but less typing for the user.

Contributor
Posts: 30

Re: Optional Criteria

That was a mistake. Long week...:smileyblush:Smiley Sad:smileyshocked:

Do you think "SAS Macro Facility, Data Step and SAS Language Elements" would be better? Let me know and I'll move it.

Yeah, I can see that, display the options, then use if statements to set the right value. It should work, even if not very eloquent.

Keep in mind I'm keeping it limited and simplified in order to be able to easily discuss and understand the solution then utilize it on a more grand scale.

This is about optional criteria overall. What if I needed to drop the criteria all together. Like if I wanted all products sold to a specific payer, regardless of type of payment or lateness?


(So drop the  and    Warehouse.Product_Sold.&Criteria. clause all together, or at lease have the system ignore it?)

SAS Super FREQ
Posts: 8,743

Re: Optional Criteria

Hi,

Yes, I think that if you are likely to get any responses at all, that the SAS Macro Facility (etc) forum is the better place to post your question. Just FYI, I haven't used %window and %display since 1993, so it's not the most current technology. Most folks do use the Prompt Manager in EG. It seems to me like you actually have a macro coding question, not really a %window question.  I would use macro logic to control how the where clause is built, but just breaking down and making them select a choice of 1 for the Late criteria and 2 for the Pay_Type criteria. Something like this in a macro program:

%if &want = 1 %then %do;

    %let whcl = where Warehouse.Product_Sold.Payer=%unquote(%str(%'&payer%')) and Warehouse.Product_Sold.Late='0' ;

%end;

%else &want=2 %then %do;

  %let whcl = where Warehouse.Product_Sold.Payer=%unquote(%str(%'&payer%')) and Warehouse.Product_Sold.Pay_Type='CC';

%end;

%else %do; %let whcl=; %end;

then in your final query, you would have something like:

Proc SQL;

create table PayerProductReport as

Select     Warehouse.Product_Sold.Product,

              Warehouse.Pruduct_Sold.Payer

From       Warehouse.Product_Sold

  &whcl

;

Quit;

This is a more conservative approach, but it does give you a whole syntactically correct WHERE clause without worrying about the possible concatenation of invalid values in the WHERE clause that you are building. Also, this would avoid the issue of having to validate the presence and values for &Criteria, because if &criteria is null or blank or an invalid or misspelled column name, it could cause your query to fail.

But you can only use %IF in a macro program and depending on your operating system and how you are displaying your %window/%display, you may run into timing issues. Since you have a specific usage question and since the use of %window/%display is not as heavily used these days, you might want to open a track with Tech Support on this.

You could post in the Macro forum, but you could also open a track with Tech Support.

cynthia

Contributor
Posts: 30

Re: Optional Criteria

I can see where you are going with that.

The %Window/%display isn't needed if there is a better way to code it.

It was just the first solution I was able to come up with when I was trying to establish a user input prompt for the &payer. variable.

Without the option of prompt manager in EG being available, what would you suggest instead?

Regular Contributor
Posts: 244

Re: Optional Criteria

Frankly if you're writing programs that expect user input, EG is the right answer.  That's why the other options are largely deprecated - not only is SAS trying to push EG as the development platform, but it's actually much better for this sort of thing. Personally I either use EG prompts, or if I'm writing for DM sessions, I put %let statements at the top of the program (or in a separate driver program) and expect the users to edit these files rather than have an interactive window.  That ensures everything timing-wise is easily handled, and requires the users to get a bit of familiarity with code (but not that much).  But since I don't really know your specific needs or situation, I don't know that I can say what would work for you.  Cynthia's suggestion of pinging Tech Support is a good idea.

Contributor
Posts: 30

Re: Optional Criteria

Unfortunately, EG just isn't an option for me. :smileycry:

I'm still new to everything. (Hence originally posting in wrong forum... Sorry about that)

How do you "ping tech support"?


I assume you're not referring to my company's internal tech support but SAS tech support?

Super User
Super User
Posts: 6,500

Re: Optional Criteria

Click on the link "submit a problem report" in the footer of this page.

Ask a Question
Discussion stats
  • 9 replies
  • 419 views
  • 0 likes
  • 4 in conversation