Complex Query (for me at least!)

deleted_user — Thu, 09 Jul 2009 14:51:32 GMT

hello all,

I have very complex query to make (for me at least!)

I have (example):
A B C
05-06-2009 1 10
05-06-2009 1 20
05-06-2009 2 20
06-06-2009 1 40
06-06-2009 1 40
06-06-2009 2 50
06-06-2009 2 50
06-06-2009 2 30
06-06-2009 2 20
07-06-2009 1 20
07-06-2009 2 10
07-06-2009 3 30
08-06-2009 3 30

The goal is to create a D and E column with range of amounts and accumulated total per day (A) for each (B) custumer

accumulated total per day (A) for each (B) custumer has to be > 10

ranges:
10>= accumulated total per day (A) for each (B) custumer < 20
20>= accumulated total per day (A) for each (B) custumer < 30
and so on!

A B D E
05-06-2009 1 20 20=<30
06-06-2009 1 40 x=>40
07-06-2009 1 20 20=<30
05-06-2009 2 20 20=<30
06-06-2009 2 150 x=>40
07-06-2009 2 10 10=<20
07-06-2009 3 30 30=<40
08-06-2009 3 30 30=<40

It has to be done in proc sql :s
any ideas?! I'm sorry that I can't explain better but I don't have access for now to SAS

thanks in advance!

Re: Complex Query (for me at least!)

DanielSantos — Thu, 09 Jul 2009 15:15:48 GMT

Hmmm... You just have about a millions ways to do this with SAS.

Anyway, It's just a simple grouping case, I wouldn't call it a complex problem.

But for the resulting table:

A B D E
05-06-2009 1 20 20=<30
06-06-2009 1 40 x=>40
07-06-2009 1 20 20=<30
05-06-2009 2 20 20=<30
06-06-2009 2 150 x=>40
07-06-2009 2 10 10=<20
07-06-2009 3 30 30=<40
08-06-2009 3 30 30=<40

I understand that E would be the range for the group (each day), and what about D?
And also which B should be chosen for each group?

Just for A and E, try this:

proc sql noprint;
create table RESULT as
select A, min(C) as MINC, max(C) as MAXC from LIB.DATA;
quit;

This will give you for each day, and the minimum and maximum values of C (range).

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

topic Re: Complex Query (for me at least!) in SAS Programming

Complex Query (for me at least!)

Re: Complex Query (for me at least!)