I have this SQL code I need to convert t SAS (below). I did not write it.
The data needs to be subtotaled by mbr_id and member_order and needs to be exported as a SAS dataset, not a result (like proc report). Any help is appreciated!
@SASuser8564 wrote:
I have this SQL code I need to convert t SAS (below). I did not write it.
...
The data needs to be subtotaled by mbr_id and member_order and needs to be exported as a SAS dataset, not a result (like proc report). Any help is appreciated!
Is that what the code does? Cursor logic is a pain to decrypt.
It looks like it's keeping information from the last or first row as well?
If you can convert to requirements and then requirements to SAS that's more efficient.
Examples of input data set and the desired output data set and description of the logic is likely going to get a better answer quicker. For one thing someone has to be familiar with that flavor of SQL just to follow the code shown (I know I can't).
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.
The data set can have dummy values for anything sensitive (names for example)but should otherwise behave like your actual data.
You might consider reducing the variables to just the ones that are manipulated as if you are selecting a bunch variables that just pass through as group then one variable to represent the bunch should suffice.
You can run this code as it is from SAS using SQL Passthru. No need to convert it at all. Just add a SELECT * on the end. Try this (change your LIBNAME statement to suit):
libname MyDB odbc noprompt = "server=MySQLSrvr;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes;";
proc sql;
connect using MyDB;
create table data_final as
select *
from connection to MyDB (
DROP TABLE #data_final
--GO
DECLARE @Flag INT
SET @Flag = 1
DECLARE @Line int
SET @Line = 1
CREATE TABLE #data_final
(
line int,
rpt_order int,
MBR_MAID varchar(15),
Member_id varchar(15),
Member_Name varchar(255),
Provider_Name varchar(255),
Network_Status varchar(255),
CHECK_NUMBER varchar(255),
CHECK_DATE date,
CLAIM_ID varchar(12),
REVENUE_CODE float,
Service_Description varchar(255),
DIAGNOSIS_CODES varchar(255),
SERVICE_FROM_DT date,
SERVICE_THROUGH_DT date,
SUM_OF_DAYS float,
SUM_OF_CLAIM_AMT float,
SUM_OF_PAID_AMT float,
PAYOR varchar(255),
Rate float
);
WHILE (@Flag <= (SELECT COUNT(*) FROM #data_order))
BEGIN
IF ((SELECT MBR_MAID FROM #data_order WHERE ROW = (@Flag)) != (SELECT MBR_MAID FROM #data_order WHERE ROW = (@Flag+1)) --last row of patientID
OR (@Flag = (SELECT COUNT(*) FROM #data_order))) --or LAST ROW of table
BEGIN
INSERT INTO #data_final (line, rpt_order, MBR_MAID, Member_id, Member_name, Provider_Name, Network_Status, CHECK_NUMBER, CHECK_DATE, CLAIM_ID, REVENUE_CODE, Service_Description, DIAGNOSIS_CODES,
SERVICE_FROM_DT, SERVICE_THROUGH_DT, SUM_OF_DAYS , SUM_OF_CLAIM_AMT, SUM_OF_PAID_AMT, PAYOR, Rate)
SELECT @Line, Member_Order, MBR_MAID, MBR_MAID, Member_name, Provider_Name, 'In Network' as Network_status, CHECK_NUMBER, CHECK_DATE, CLAIM_ID, REVENUE_CODE, 'MH-InPatient-Psychiatric Srvcs R&B' as Service_Description, DIAGNOSIS_CODES,
SERVICE_FROM_DT, SERVICE_THROUGH_DT, SUM_OF_DAYS, SUM_OF_CLAIM_AMT, SUM_OF_PAID_AMT, PAYOR, Rate FROM #data_order where ROW = @Flag;
SET @Line = @Line + 1
--INSERT TOTAL
INSERT INTO #data_final(line, rpt_order, MBR_MAID, Member_id, Member_name, Provider_Name, Network_Status, CHECK_NUMBER, CHECK_DATE, CLAIM_ID, REVENUE_CODE, Service_Description, DIAGNOSIS_CODES,
SERVICE_FROM_DT, SERVICE_THROUGH_DT, SUM_OF_DAYS , SUM_OF_CLAIM_AMT, SUM_OF_PAID_AMT, PAYOR, rate)
SELECT @Line, Member_Order, MBR_MAID, '0' as Member_id, '' as Member_name, '' as Provider_Name, '' as Network_Status, '' as CHECK_NUMBER, '' as CHECK_DATE, '' as CLAIM_ID, '' as REVENUE_CODE, '' as Service_Description,
'Total' as DIAGNOSIS_CODES, '' as SERVICE_FROM_DT, '' as SERVICE_THROUGH_DT, SUM_OF_DAYS, SUM_OF_CLAIM_AMT, SUM_OF_PAID_AMT, '' as PAYOR, '' as Rate
FROM #data_totals
WHERE MBR_MAID = (SELECT MBR_MAID FROM #data_order where ROW = (@Flag))
GROUP BY Member_Order, MBR_MAID, Principle_Diag, SUM_OF_DAYS, SUM_OF_CLAIM_AMT, SUM_OF_PAID_AMT;
SET @Line = @Line + 1
END
ELSE
BEGIN
INSERT INTO #data_final (line, rpt_order, MBR_MAID, Member_id, Member_name, Provider_Name, Network_Status, CHECK_NUMBER, CHECK_DATE, CLAIM_ID, REVENUE_CODE, Service_Description, DIAGNOSIS_CODES,
SERVICE_FROM_DT, SERVICE_THROUGH_DT, SUM_OF_DAYS , SUM_OF_CLAIM_AMT, SUM_OF_PAID_AMT, PAYOR, Rate)
SELECT @Line, Member_Order, MBR_MAID, MBR_MAID, Member_name, Provider_Name, 'In Network' as Network_status, CHECK_NUMBER, CHECK_DATE, CLAIM_ID, REVENUE_CODE,'MH-InPatient-Psychiatric Srvcs R&B' as Service_Description, DIAGNOSIS_CODES,
SERVICE_FROM_DT, SERVICE_THROUGH_DT, SUM_OF_DAYS, SUM_OF_CLAIM_AMT, SUM_OF_PAID_AMT, PAYOR, Rate FROM #data_order where ROW = @Flag;
SET @Line = @Line + 1
END
SET @Flag = @Flag + 1
END
select *
from #data_final
);
disconnect from MyDB;
quit;
I think what your SQL code does is something like this:
Data data_final;
length
line 8
rpt_order 8
MBR_MAID $15
Member_id $15
Member_Name $255
Provider_Name $255
Network_Status $255
CHECK_NUMBER $25
CHECK_DATE 8
CLAIM_ID $12
REVENUE_CODE 8
Service_Description $255
DIAGNOSIS_CODES $255
SERVICE_FROM_DT 8
SERVICE_THROUGH_DT 8
SUM_OF_DAYS 8
SUM_OF_CLAIM_AMT 8
SUM_OF_PAID_AMT 8
PAYOR $255
Rate 8
;
format
line 8.0
rpt_order 8.0
CHECK_DATE yymmdd10.
SERVICE_FROM_DT yymmdd10.
SERVICE_THROUGH_DT yymmdd10.
;
keep line--rate;
set data_order(in=base) data_totals(in=sum);
by MBR_MAID;
line=_N_;
if base then do;
rpt_order=Member_Order;
Member_id=MBR_MAID;
Network_Status='In Network';
Service_Description='MH-InPatient-Psychiatric Srvcs R&B';
end;
else do;
rpt_order=Member_Order;
Member_id=0;
call missing(Member_Name,Provider_Name,Network_Status,CHECK_NUMBER,CHECK_DATE,CLAIM_ID,REVENUE_CODE,Service_Description,SERVICE_FROM_DT,SERVICE_THROUGH_DT,PAYOR,Rate);
DIAGNOSIS_CODES='Total';
end;
run;
The first lines (down to and including the KEEP statement) is analogue to the CREATE TABLE statement.
The code assumes that both DATA_ORDER and DATA_TOTALS are sorted by MBR_MAID.
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.