topic Re: Converting SQL to SAS in SAS Programming

Converting SQL to SAS

SASuser8564 — Wed, 14 Jun 2023 16:14:39 GMT

I have this SQL code I need to convert t SAS (below). I did not write it.

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

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!

Re: Converting SQL to SAS

Reeza — Wed, 14 Jun 2023 16:24:01 GMT

@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.

Re: Converting SQL to SAS

ballardw — Wed, 14 Jun 2023 16:33:53 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Converting SQL to SAS

SASKiwi — Wed, 14 Jun 2023 20:11:46 GMT

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;

Re: Converting SQL to SAS

s_lassen — Thu, 15 Jun 2023 07:13:47 GMT

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.