BookmarkSubscribeRSS Feed
SASuser8564
Calcite | Level 5

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! 

4 REPLIES 4
Reeza
Super User

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

 

ballardw
Super User

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.

SASKiwi
PROC Star

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;
s_lassen
Meteorite | Level 14

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: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 749 views
  • 3 likes
  • 5 in conversation