BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
InfoAlisaA
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
FloydNevseta
Pyrite | Level 9

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

View solution in original post

5 REPLIES 5
Astounding
PROC Star

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.

InfoAlisaA
Calcite | Level 5

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

FloydNevseta
Pyrite | Level 9

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

InfoAlisaA
Calcite | Level 5

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1570 views
  • 0 likes
  • 3 in conversation