BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
johnjinkim
Obsidian | Level 7

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

3 REPLIES 3
Reeza
Super User

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
Barite | Level 11

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
Quartz | Level 8 hbi
Quartz | Level 8

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

 

/* 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;

 side_by_side.gif

 wanted_paid_denied.gif

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 16190 views
  • 3 likes
  • 4 in conversation