topic SQL Order By Statement in SAS Programming

SQL Order By Statement

johnjinkim — Tue, 06 Oct 2015 14:51:11 GMT

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!

Re: SQL Order By Statement

Reeza — Tue, 06 Oct 2015 15:19:55 GMT

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

Aggregate the amounts into a paid total and sort file by PaidTotal
Assign an order variable to each code
Merge the order variable in, by merging on code.
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;

Re: SQL Order By Statement

Steelers_In_DC — Tue, 06 Oct 2015 16:02:54 GMT

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;

Re: SQL Order By Statement

hbi — Tue, 06 Oct 2015 19:02:51 GMT

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;