Random Sampling with conditions

[Shailendram]

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.

Thanks in advance,

Shailendra

[Kurt_Bremser · Accepted solution]

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.

[Shailendram]

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

[Kurt_Bremser]

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

[Ksharp]

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;
read all var{make model invoice};
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;