topic Re: %DO %UNTIL in PROC SQL in SAS Programming

%DO %UNTIL in PROC SQL

rodrichiez — Wed, 05 Jun 2019 18:44:59 GMT

Hello,

I'm trying to find a way to solved this: I need to create a table that contains all the clients that reach the 80% of the budget grouped by manager.

I did something like:

%macro ochenta ();

proc sql noprint; select budget_80 into :valor from work.QUERY_FOR_BALANCES1 where officer_id='735' and Budget_Category_Desc="Captaciones";
%put &valor;

%DO %until (&valor=&valor);
CREATE TABLE Clients as
SELECT t2.Customer_Id, T2.Current_Balance_Dop, SUM(t1.Current_Balance_Dop) as Total_Balance  FROM work.balances T1
left join (select t2.Customer_Id, t2.Current_Balance_Dop
FROM work.balances T2 where t2.Budget_Category_Desc="Captaciones" and t2.officer_id='735'
and t2.Current_Balance_Dop NOT =0) T2 on t1.Customer_Id=T2.Customer_Id
where t1.Budget_Category_Desc="Captaciones" and t1.officer_id='735'
group by t2.Customer_Id, t2.Current_Balance_Dop
having (CALCULATED Total_Balance) <= &valor;

%end;

%mend;
%ochenta()

But the result is a list of all the clients in that manager id.

Re: %DO %UNTIL in PROC SQL

rodrichiez — Wed, 05 Jun 2019 20:18:01 GMT

I need all the clients from table A that the sum of their balances reach the 80% of the manager budget on table B.

Re: %DO %UNTIL in PROC SQL

ChrisNZ — Thu, 06 Jun 2019 03:44:36 GMT

Your question is unclear and your code is a mess and you supply no data.

> I need all the clients from table A that the sum of their balances reach the 80% of the manager budget on table B.;

Like this?

data CLIENTS;
  CL=1; BAL=2; output;
  CL=1; BAL=2; output;
  CL=2; BAL=3; output;
run;
data BUDGET;
  MGR='a'; CL=1; BUDGET=5; output;
  MGR='b'; CL=2; BUDGET=4; output;
run;
proc sql;
   select a.*, b.MGR, sum(BAL) as SUM
   from CLIENTS a, BUDGET b
   where a.CL=b.CL 
   group by MGR
   having calculated SUM >= BUDGET*.8;
quit;

CL	BAL	MGR	SUM
1	2	a	4
1	2	a	4

Re: %DO %UNTIL in PROC SQL

andreas_lds — Thu, 06 Jun 2019 05:10:39 GMT

Before using macro code, you need tested and error-free code. There is hardly any reason to use macro-code at all, except for automation or process-flow-control. The statement

%DO %until (&valor=&valor);

will always be true, so your loop stops after the first iteration.

Your problem seems to be solvable with a single select-statement, maybe using having. But i need to see what you have and what you expect as result to suggest code.

Re: %DO %UNTIL in PROC SQL

rodrichiez — Thu, 06 Jun 2019 15:48:15 GMT

Sorry for the mess.

An example:

I've got this manager with a bugdet of $861,512,982.00 and another table with list of clients and their balances

TABLE A

M1 Budget= $861,512,982.00

MID = 952

TABLE B

MID = 952

C1 BAL=1,590

C2 BAL=2,590

C3 BAL=150,000.00

C4 BAL=500,000.00

....................................

And I need to sum the balances of the clients until the total sum reach or its near to 861,512,982.00, and the two tables only joined by MID column.

Hope this help to explain what I am looking for.

Re: %DO %UNTIL in PROC SQL

Tom — Thu, 06 Jun 2019 16:18:23 GMT

@rodrichiez wrote:

Sorry for the mess.

An example:

I've got this manager with a bugdet of $861,512,982.00 and another table with list of clients and their balances

TABLE A

M1 Budget= $861,512,982.00

MID = 952

TABLE B

MID = 952

C1 BAL=1,590

C2 BAL=2,590

C3 BAL=150,000.00

C4 BAL=500,000.00

....................................

And I need to sum the balances of the clients until the total sum reach or its near to 861,512,982.00, and the two tables only joined by MID column.

Hope this help to explain what I am looking for.

Sounds like you mean you want to allocate your budget to clients until you reach 80% of the budgeted amount. What about the rest of the projects/clients for that manager? Is there any order that you want pick the "winners" in the assignment? Is there a date variable to use? Do you want to add them largest to smallest? What happens if the next client would push the total over 80% (or over 100%)? Do you want to just skip that one and try the next one?

SQL is probably NOT the right tool for this. There are optimization procedures that SAS has (if you have the right products licensed.)

Otherwise just do it with a data step.

Re: %DO %UNTIL in PROC SQL

rodrichiez — Thu, 06 Jun 2019 18:55:04 GMT

Hello @Tom

Here are my answers:

What about the rest of the projects/clients for that manager?

I just need the clients that represents the 80% of the budget.

Is there any order that you want pick the "winners" in the assignment?

The ones that has the greater current balance.

Is there a date variable to use?

I filtered the table to the last available date.

Do you want to add them largest to smallest?

Yes.

What happens if the next client would push the total over 80% (or over 100%)? Do you want to just skip that one and try the next one?

It doesn't matter if the 80% percent is in one or two clients, we want to know how many customers represent that percentage, but the total sum can't exceed the 80% of the budget for that manager.

I tried with data step as you said but I'm not very familiar with.

Re: %DO %UNTIL in PROC SQL

Tom — Thu, 06 Jun 2019 20:35:54 GMT

Doesn't look very hard to me. Let's adjust your sample data so that the total costs are more in line with the budget.

data A;
 input mid budget;
cards;
952 800000.00
;
data B;
  input mid client $ cost ;
cards;
952 C1 1590
952 C2 2590
952 C3 150000
952 C4 500000
;

So just combine the two so you can see the budget value and generate a running total. Then you can check if the running total has exceeded any fraction of the budget you want.

data want;
  merge a b ;
  by mid;
  running_total+cost;
  if first.mid then running_total=cost;
  percent = 100*running_total/budget;
  over = percent >= 80 ;
run;

proc print;
 format budget cost running_total comma14.2 ;
 format percent 7.2 ;
run;

Results:

                                                     running_
Obs    mid      budget      client       cost         total       percent    over

 1     952    800,000.00      C1        1,590.00      1,590.00      0.20       0
 2     952    800,000.00      C2        2,590.00      4,180.00      0.52       0
 3     952    800,000.00      C3      150,000.00    154,180.00     19.27       0
 4     952    800,000.00      C4      500,000.00    654,180.00     81.77       1

Re: %DO %UNTIL in PROC SQL

rodrichiez — Fri, 07 Jun 2019 11:44:56 GMT

Thank you Tom.
I need to learn more about Data Step.