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
Remove the asterisks around your macro parms in the %index function.
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.
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.
Remove the asterisks around your macro parms in the %index function.
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!!
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.