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
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.
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.
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
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
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!