Help using Base SAS procedures

Data-Driven Parameter Validation Code Check

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Data-Driven Parameter Validation Code Check

Hello Everyone,

I am working on this issue where I am trying to use a macro to validate if the custtype is not null and is found in the macro that I created in my SQL script.

The second half of the program is working where if the custtype is null where it writes out to the Log, but the first half of my program just isn't running the proc print script.

Could someone take a look at my code and let me know where I am going wrong with this?

options mlogic /*nosymbolgen*/ ;

%macro listing(custtype);

proc sql noprint;

   select Customer_Type_ID into :IDLIST separated by ' '

      from orion.customer_type;

quit;

%if %index(*&IDLIST*, *&custtype*) > 0 %then %do;

     proc print data=orion.customer noobs;

         where Customer_Type_ID=put(&custtype,best.);

         var Customer_ID Customer_Name Customer_Type_ID;

         title "Customer Type: &custtype";

      run;

   %end;

   %else %do;

    %put Value for CUSTTYPE is invalid;

    %put Valid TYPE values are &IDLIST;

%end;

%mend listing;

%listing(1020)

%listing()

Thanks!

Alisa


Accepted Solutions
Solution
‎03-27-2012 05:12 PM
Frequent Contributor
Posts: 101

Re: Data-Driven Parameter Validation Code Check

Remove the asterisks around your macro parms in the %index function.

View solution in original post


All Replies
Super User
Posts: 5,069

Data-Driven Parameter Validation Code Check

Alisa,

It's good practice to add delimiters, as you have, when using %INDEX.  But you have to prepare the variables properly to produce matches.  For example, try:

select trim(Customer_Type_ID) into : IDLIST separated by '*'

That should do it unless there's something else that I missed.

Good luck.

Frequent Contributor
Posts: 90

Data-Driven Parameter Validation Code Check

Hello Astounding,

I put this code in:

options mlogic /*nosymbolgen*/ ;

%macro listing(custtype);

proc sql noprint;

   select distinct trim(Customer_Type_ID) into :IDLIST separated by '*'

      from orion.customer_type;

quit;

%if %index(*&IDLIST*, *&custtype*) > 0 %then %do;

     proc print data=orion.customer noobs;

         where Customer_Type_ID=put(&custtype,best.);

         var Customer_ID Customer_Name Customer_Type_ID;

         title "Customer Type: &custtype";

      run;

   %end;

   %else %do;

    %put Value for CUSTTYPE is invalid;

    %put Valid TYPE values are &IDLIST;

%end;

%mend listing;

%listing(1020)

%listing()

and I got the following message in my Log:

ERROR: Function TRIM requires a character expression as argument 1.

This didn't seem to work. Smiley Sad

Solution
‎03-27-2012 05:12 PM
Frequent Contributor
Posts: 101

Re: Data-Driven Parameter Validation Code Check

Remove the asterisks around your macro parms in the %index function.

Frequent Contributor
Posts: 90

Data-Driven Parameter Validation Code Check

THANK YOU!!! :smileygrin: This is my final code:

%macro listing(custtype);

proc sql noprint;

   select distinct Customer_Type_ID into :IDLIST separated by ' '

      from orion.customer_type;

quit;

%if %index(&IDLIST, &custtype) > 0 %then %do;

     proc print data=orion.customer noobs;

         where Customer_Type_ID=&custtype;

         var Customer_ID Customer_Name Customer_Type_ID;

         title "Customer Type: &custtype";

      run;

   %end;

   %else %do;

    %put Value for CUSTTYPE is invalid;

    %put Valid TYPE values are &IDLIST;

%end;

%mend listing;

%listing(1020)

%listing()

Thanks for your help!!

Super User
Posts: 5,069

Data-Driven Parameter Validation Code Check

Alisa,

Unfortunately, you may be getting the wrong answer.  For example, assuming that 1020 is a valid customer type, but 102 is not valid, try running the macro:

%listing (102)

The problem is that the %INDEX function looks for strings of characters "102" not for words.  I think there is an INDEXW function, but you would have to check that, and would have to add %sysfunc to use it.  Or, you could correct my solution by switching to:

strip(put(customer_type_id, 10.))

instead of:

trim(customer_type_id)

Good luck.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 796 views
  • 0 likes
  • 3 in conversation