A confusion with quote function in sas macro

[duanzongran]

dear ALL:

I want to write a conditional select macro to select something from a dataset with a conditional statement,here is the code;

data cust_info;
informat application_no $5. city $8.   grade $2. payment_proportion best18. pp_grp $18.;
input application_no $ city $  grade $ payment_proportion ;
if payment_proportion<0.3 then pp_grp='(LOW,30%]';
else if payment_proportion<0.3 then pp_grp='(30%,60%]';
else pp_grp='(60%,HIGH)';
datalines ;
N0001  city4  G3  0.954791131
N0002  city2  G2  0.384459679
N0003  city3  G3  0.924944204
N0004  city4  G1  0.890577525
N0005  city4  G2  0.167083544
N0006  city1  G3  0.552686255
N0007  city4  G1  0.616079916
N0008  city2  G3  0.721666748
N0009  city3  G3  0.219522284
N0010  city1  G2  0.736888981
N0011  city1  G3  0.52245841
N0012  city4  G1  0.808448548
N0013  city4  G3  0.737899701
N0014  city4  G2  0.037866378
N0015  city2  G3  0.196772325
N0016  city4  G2  0.248803462
N0017  city2  G2  0.117926269
N0018  city3  G3  0.145969656
N0019  city2  G2  0.554093061
N0020  city2  G3  0.09314399
N0021  city1  G3  0.043598302
N0022  city3  G2  0.743726176
N0023  city3  G2  0.475828188
N0024  city4  G3  0.219011228
N0025  city1  G3  0.230165184
N0026  city3  G2  0.883160772
N0027  city1  G2  0.033940403
N0028  city2  G2  0.248249667
N0029  city4  G1  0.742306159
N0030  city4  G3  0.271939134
N0031  city2  G2  0.531722203
N0032  city1  G1  0.274051488
N0033  city4  G1  0.95289881
N0034  city4  G1  0.42693408
N0035  city2  G3  0.955515343
N0036  city3  G1  0.143521235
N0037  city3  G3  0.936618931
N0038  city4  G3  0.86678435
N0039  city3  G2  0.367460855
N0040  city4  G3  0.681624734
N0041  city3  G1  0.056459049
N0042  city3  G2  0.206450467
N0043  city3  G3  0.982449115
N0044  city1  G3  0.852173721
N0045  city2  G1  0.082348749
N0046  city1  G3  0.643541203
N0047  city3  G3  0.555692983
N0048  city2  G3  0.765327052
N0049  city3  G3  0.443996454
N0050  city4  G1  0.283174744
;
run;
%macro conditional_select(condition_statement=,label=);
proc sql;
create table result as
select city,count(1) as cnt label="&label."
from Cust_info(where=(&condition_statement.))
group by city;
quit;
%mend;
*now I want to make a summary from cust_info where grade=G3 and pp_grp=(30%,60%],and give label  a value.
"(30%,60%]&G3",here "&" just represent "and" ,not  to  be resolved to &G3 as a macro variable  ;
%conditional_select(condition_statement=%nrstr(grade=G3 and pp_grp=(30%,60%]),label=%nrstr((30%,60%]&G3));

But it failed and SAS has no response . Meanwhile，SAS can't run other command code.I have to restart it.

I have two questions :

1. Why this happen? When sas has no response ,what should I do to  get back to normal?

2. How to make my code work ? In fact I am very confused with quote function in sas macro.

Thank you for your patience !ANY suggestion is welcomed！

[Quentin · Accepted solution]

Hi,

Macro quoting is a hard subject.  Because you have an unmatched parenthesis in your value, if you want to quote it, you need to mark it with a % sign.  You are also missing actual " marks in your value for condition_statement. 

Try:

%conditional_select(
  condition_statement=grade="G3" and pp_grp="(30%,60%]"
  ,label=%nrstr(%(30%,60%]&G3)
  )

But often life is easier if you design your macro to avoid the need for macro quoting.  For example, if you change your macro definition to make the user pass the quote marks around the value for label, i.e. change to:

%macro conditional_select(condition_statement=,label=);
proc sql;
create table result as
select city,count(1) as cnt label=&label  /*remove quote marks here*/
from Cust_info(where=(&condition_statement.))
group by city;
quit;
%mend;

Then the call to the macro doesn't need macro quoting.  You can call it like:

%conditional_select(
  condition_statement=grade="G3" and pp_grp="(30%,60%]"
  ,label='(30%,60%]&G3'
  )

In that call, no macro quoting is needed because the actual quotation marks hide the unmatched parentheses and the single quotation marks hide &G3 from being seen as a macro trigger.

Both queries return 0 records, but I'll let you explore that. : )

When SAS is non responsive often the problem is an unmatched quote or unmatched parenthesis.  You can try submitting a magic string like:

*';*";*))%*))*/;%mend; data foo;run;

To see if that will close out whatever is open.  But often the best solution is to restart your session.

[duanzongran]

Quentin ! Thank YOU for your kindness! You inspired me.
I made some elementary mistakes including :
misunderstanding character string
and
the wrong if - then sencense.Now I fix it in the reply.

[PaigeMiller]

You can make your life a whole lot easier by not having complicated strings in your search, especially when the complicated strings are derived from simple IF THEN statements.

First let's fix an error here:

if payment_proportion<0.3 then pp_grp='(LOW,30%]';
else if payment_proportion<0.6 then pp_grp='(30%,60%]'; /* This was incorrect, I have fixed it */
else pp_grp='(60%,HIGH)';

Next, your macro MUST product valid legal working SAS code, and it does not when you have

%nrstr(grade=G3 and pp_grp=(30%,60%])

because this produces code from the macro that reads

from Cust_info(where=(grade=G3 and pp_grp=(30%,60%]))

Can you tell me why the part grade=G3 is not valid code??? Can you tell me why the part pp_grp=(30%,60%] is not valid code????

Anyway, to simplify the whole thing so you are not searching for strings with unmatched parentheses and commas and percent signs, use your original code to determine the buckets.

%conditional_select(condition_statement=grade='G3' and payment_proportion<0.3, label=<0.3)

This is a much easier thing to get right in the macro language than searching for strings with unmatched parentheses and commas and percent signs. Make your life simpler, not more complicated.

More importantly, the whole idea of creating a character string  to represent numeric values is another example of making your life more complicated, and makes me cringe. Better keep the values as numeric and assign a custom format to them, this allows you to search by numeric values instead of searching by these complicated strings.

proc format;
    value ppf low-<0.3='(LOW,30%]'
         0.3-<0.6='(30%,60%]'
         0.6-high='(60%,HIGH)';
quit;

and assign format ppf to the variable payment_proportion in a data step or PROC DATASETS — or better yet, don't assign the format to the variable until you need it to be human-readable in outputs such as tables or reports.

Lastly, if you really really really really really really really really really really really really want a label %nrstr((30%,60%]&G3) to work in your macro code, you have unmatched parentheses within %nrstr() and you have to account for that. I refer you to the %nrstr() documentation for the solution. But what is the point of labelling variable CNT as '(LOW,30%]' when it should be labelled 'COUNT'???

[Tom]

---

@PaigeMiller wrote:

...

But what is the point of labelling variable CNT as '(LOW,30%]' when it should be labelled 'COUNT'???

---

You buried the lede.  https://style.mla.org/dont-bury-the-lede

[duanzongran]

Hi PaigeMiller ,Thank you for fixing my mistakes and I will recheck my code in future.I will learn more about %nrstr() document you mentioned,Thanks again!

[PaigeMiller]

---

@duanzongran wrote:
Hi PaigeMiller ,Thank you for fixing my mistakes and I will recheck my code in future.I will learn more about %nrstr() document you mentioned,Thanks again!

---

@duanzongran 

I think you missed the point. Simplifying the search, as I showed, makes it work easily, and then %nrstr() isn't needed at all.

Your statement makes it sound as if you still intend to use the complicated values which have unmatched parentheses and percent signs and commas, which I contend is not a good thing to do.

[duanzongran]

OH ，thank you again.I think I've got your point.Try to use simple methods to achieve goals.

[duanzongran]

Thanks @PaigeMiller ， the data step should be fixed as

data cust_info;
informat application_no $5. city $8.   grade $2. payment_proportion best18. pp_grp $18.;
input application_no $ city $  grade $ payment_proportion ;
if payment_proportion<=0.3 then pp_grp='(LOW,30%]';
else if payment_proportion<=0.6 then pp_grp='(30%,60%]';
else pp_grp='(60%,HIGH)';
datalines ;
N0001  city4  G3  0.954791131
N0002  city2  G2  0.384459679
N0003  city3  G3  0.924944204
N0004  city4  G1  0.890577525
N0005  city4  G2  0.167083544
N0006  city1  G3  0.552686255
N0007  city4  G1  0.616079916
N0008  city2  G3  0.721666748
N0009  city3  G3  0.219522284
N0010  city1  G2  0.736888981
N0011  city1  G3  0.52245841
N0012  city4  G1  0.808448548
N0013  city4  G3  0.737899701
N0014  city4  G2  0.037866378
N0015  city2  G3  0.196772325
N0016  city4  G2  0.248803462
N0017  city2  G2  0.117926269
N0018  city3  G3  0.145969656
N0019  city2  G2  0.554093061
N0020  city2  G3  0.09314399
N0021  city1  G3  0.043598302
N0022  city3  G2  0.743726176
N0023  city3  G2  0.475828188
N0024  city4  G3  0.219011228
N0025  city1  G3  0.230165184
N0026  city3  G2  0.883160772
N0027  city1  G2  0.033940403
N0028  city2  G2  0.248249667
N0029  city4  G1  0.742306159
N0030  city4  G3  0.271939134
N0031  city2  G2  0.531722203
N0032  city1  G1  0.274051488
N0033  city4  G1  0.95289881
N0034  city4  G1  0.42693408
N0035  city2  G3  0.955515343
N0036  city3  G1  0.143521235
N0037  city3  G3  0.936618931
N0038  city4  G3  0.86678435
N0039  city3  G2  0.367460855
N0040  city4  G3  0.681624734
N0041  city3  G1  0.056459049
N0042  city3  G2  0.206450467
N0043  city3  G3  0.982449115
N0044  city1  G3  0.852173721
N0045  city2  G1  0.082348749
N0046  city1  G3  0.643541203
N0047  city3  G3  0.555692983
N0048  city2  G3  0.765327052
N0049  city3  G3  0.443996454
N0050  city4  G1  0.283174744
;
run;

[Tom]

Note that there is no need to attach any INFORMATs to any of those variables for the data step to work.  SAS does not need to use special informats to read character strings or normal numbers.  Also BEST is the name of a FORMAT, not an INFORMAT.

Also once you have already forced SAS to define the type of the variable there is no need to add the $ into the INPUT statement. 

That is only useful when the input statement is the first place the variable is reference.  Because SAS will assume a variable is numeric if you haven't given it any indication at all that it should be something else.

You could use a LENGTH statement to define the type and storage length of the variables.  Define numeric variable as length 8 so that the full 64 bit floating point values SAS uses can be stored.

length application_no $5 city $8 grade $2 payment_proportion 8 pp_grp $18;
input application_no city grade payment_proportion ;

[duanzongran]

THANK YOU TOM! Yes,you point out my long time confusions. I will try hard to understand "informat" vs "format","length" and "input".