SQL Order By Statement

[johnjinkim]

Thanks in advance for reviewing this potentially straightforward solution. I did do a search before posting.

Question: How would I go about using SQL to create a table that lists the highest paid codes first, but then the corresponding Denied amounts for the table directly after that corresponding code.

data  have;
input  status  code $  PaidAmount : dollar.;
format      PaidAmount dollar.;
cards;
Paid                     81511          $1000           
Paid                     81317          $500
Paid                     81400          $300
Paid                     81511          $2000
Denied                   81511          $0
Denied                   81317          $0
;
run;

The table I would be looking to create from the data is:

Code        Status        PaidAmount

81511         Paid              $3000

81511         Denied           $0

81317         Paid              $500

81317         Denied           $0

81400         Paid              $300

I can create a table that lists each of the variables in priority order, but not conditionally. For example, the following code only gets me to the highest paid amounts, but then the denied lines are printed out after. I would like the denied lines to come after the corresponding code's paid line, but based on the highest paid codes.

proc sql;

select code, status, paidamount format=dollar24.2

from have

group by code, status

order by paidamount ;

quit;

Thanks again!

[Reeza · Accepted solution]

There's probably a more direct method than this, but this works as far as I can see 🙂

1. Aggregate the amounts into a paid total and sort file by PaidTotal
2. Assign an order variable to each code
3. Merge the order variable in, by merging on code.
4. Sort file as desired.

data  have;
input  status $  code $  PaidAmount : dollar.;
format      PaidAmount dollar.;
cards;
Paid                     81511          $1000           
Paid                     81317          $500
Paid                     81400          $300
Paid                     81511          $2000
Denied                   81511          $0
Denied                   81317          $0
;
run;

proc sql;
create table order_file as
select status, code, sum(PaidAmount) as PaidTotal
from have
group by code, status
order by PaidTotal desc;
quit;

data have2;
set order_file;
where status='Paid';
order=_n_;
run;

proc sql;
create table want as
select a.*, b.order
from order_file as a
left join have2 as b
on a.code=b.code
order by order, status desc;
quit;

[Steelers_In_DC]

data  have;
input  status$  code$  PaidAmount : dollar.;
format      PaidAmount dollar.;
cards;
Paid                     81511          $1000
Paid                     81317          $500
Paid                     81400          $300
Paid                     81511          $2000
Denied                   81511          $0
Denied                   81317          $0
;
run;

proc sql;
create table prep as
select distinct code,status,sum(paidamount) as paidamount
from have
where not missing(paidamount)
group by code
order by paidamount desc,status desc;

data want;
set prep;
if status = 'Denied' then paidamount = 0;
run;

[hbi]

Below is a solution that uses a combination of a PROC SQL statement and a data step that contains a do loop. Enjoy. 

/* intermediate table - pivot "Denied" rows into columns */
PROC SQL;
  CREATE TABLE side_by_side AS 
  SELECT code
       , MAX(CASE WHEN status='Paid'   THEN status     ELSE "" END) AS PaidStatus
       , SUM(CASE WHEN status='Paid'   THEN PaidAmount ELSE .  END) AS PaidAmount
       , MAX(CASE WHEN status='Denied' THEN status     ELSE "" END) AS DeniedStatus
       , SUM(CASE WHEN status='Denied' THEN PaidAmount ELSE .  END) AS DeniedAmount
  FROM Have
  GROUP BY Code
  ORDER BY PaidAmount DESC, code;
QUIT;


DATA want(DROP=PaidStatus DeniedStatus DeniedAmount);
  ATTRIB Code Status PaidAmount label='';
  SET side_by_side;
  ARRAY _statusArray PaidStatus DeniedStatus;
  DO OVER _statusArray;
    Status = _statusArray;
    IF Status = 'Denied' THEN DO;
      PaidAmount = DeniedAmount;
    END;
    IF NOT MISSING(Status) THEN OUTPUT;
  END;
RUN;