Solved
New Contributor
Posts: 2

# Random Sampling with conditions

[ Edited ]

Hi there,

I would be really thankfull if you can help me with following query.

I have customer list with their numbers of transactions(No of Invoices) and their spend. And I want select 10% random customers from this sample which have almost 10% spend and transactions (taking 9 to 11% to get a solution). Basically I want to keep on creating the random samples untill my conditions are met.

Following is the code.

%let Trans_Ratio=1;
%let Spend_Ratio=1;

%do %until(0.09<=Trans_Ratio<=0.11 %and 0.09<=Spend_Ratio<=0.11);

proc surveyselect data=All_data samprate=0.1 method=srs out=Sample_data;
run;

proc sql;
select sum(NoOfInvoices) into : Sample_Invoice from Sample_data;
select sum(Revenue) into : Sample_Spend from Sample_data;
select sum(NoOfInvoices) into : Tot_Invoice from All_data;
select sum(Revenue) into : Tot_Spend from All_data;
quit;

%let Trans_Ratio=%sysevalf(&Sample_Invoice./&Tot_Invoice.);
%let Spend_Ratio=%sysevalf(&Sample_Spend./&Tot_Spend.);

%end;

But the problem is that the loop is running even if the condtitions(0.09<=Trans_Ratio<=0.11 %and 0.09<=Spend_Ratio<=0.11) are met.

SYMBOLGEN: Macro variable TRANS_RATIO resolves to 0.09950702848001
SYMBOLGEN: Macro variable SPEND_RATIO resolves to 0.10113175882141
MLOGIC(TG_CREATION): %IF condition ((0.09<=&Trans_Ratio.<=0.11) %and (0.09<=&Spend_Ratio.<=0.11)) is FALSE

Not sure why the if statment is FALSE.

Shailendra

Accepted Solutions
Solution
‎10-04-2016 05:33 AM
Super User
Posts: 10,209

## Re: Random Sampling with conditions

[ Edited ]

Your %if condition is false because the macro processor processes TEXT.

0.09<=Trans_Ratio<=0.11

translates to

"0.09"<="Trans_Ratio"<= "0.11"

Since the letter "T" can never be "smaller" than the letter(!) "0", the condition will never be true.

To evaluate a numeric expression in macro, you have to use the %eval function:

``````%macro test;
%let testvar=0.1;
%if 0.09 < &testvar <0.11 %then %put yes1;
%if %eval(0.09 < &testvar) and %eval(&testvar <= 0.11) %then %put yes2;
%mend;
%test;``````

You will find that the first line does not return yes1, but the second returns yes2.

You also missed the fact that to address a macro variable, you have to place the ampersand before its name.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎10-04-2016 05:33 AM
Super User
Posts: 10,209

## Re: Random Sampling with conditions

[ Edited ]

Your %if condition is false because the macro processor processes TEXT.

0.09<=Trans_Ratio<=0.11

translates to

"0.09"<="Trans_Ratio"<= "0.11"

Since the letter "T" can never be "smaller" than the letter(!) "0", the condition will never be true.

To evaluate a numeric expression in macro, you have to use the %eval function:

``````%macro test;
%let testvar=0.1;
%if 0.09 < &testvar <0.11 %then %put yes1;
%if %eval(0.09 < &testvar) and %eval(&testvar <= 0.11) %then %put yes2;
%mend;
%test;``````

You will find that the first line does not return yes1, but the second returns yes2.

You also missed the fact that to address a macro variable, you have to place the ampersand before its name.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 2

## Re: Random Sampling with conditions

Thanks a lot for the solution.

This works perfectly fine but this is a brute force method and might not be effective everytime. Is there any way I can create random sample which satisfy cetain conditions like in this case.

Thanks,

Shailendra

Super User
Posts: 10,209

## Re: Random Sampling with conditions

I'd try something like

- sort by descending spend

- use 1+int(random()*10) (insert a suitable random number function) to create a number "n" between 1 and 10

- take the nth record from the first 10

- repeat, starting 10 records later

- until you reach eof

that should give you 10% of the dataset with about 10% of spend, and require only one sort + one pass

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,761

## Re: Random Sampling with conditions

```Can you post an example to explain this question ?

data have;
set sashelp.cars(keep=make model invoice);
run;

proc sql noprint;
select int(count(distinct make)*0.1) as n,
0.09*sum(invoice) as low,
0.11*sum(invoice)
into :n,: low,: high
from have;
quit;

proc iml;
use have;
close;

found=0;
key=unique(make);
do until(found);
temp=sample(key,&n,'wor');
idx=loc(element(make,temp));
sum=sum(invoice[idx]);
if &low < sum & sum < &high then do;
found=1;print "Found:" "&low" sum[l=''] "&high";
end;
end;

_make=make[idx];_model=model[idx];_invoice=invoice[idx];
create want var{_make _model _invoice};
append;
close;
quit;

```
☑ This topic is solved.