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!
There's probably a more direct method than this, but this works as far as I can see 🙂
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;
There's probably a more direct method than this, but this works as far as I can see 🙂
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;
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.