topic Re: Convert SQL / teradata code to SAS in SAS Programming

Convert SQL / teradata code to SAS

rahulsaha2127 — Mon, 13 Feb 2023 07:41:31 GMT

I have the following code which I need to convert in SAS form:

create volatile table gl as (
select GEN_ID, AU_GEN_ID, TYPE_2_DESC,
TYPE_5_DESC, TYPE_8_DESC
from COMMERCIAL_V3.DATA_F
qualify row_number() over
(partition by , GEN_ID, AU_GEN_ID order by DATA_OUTSTANDING_AMT desc,
DATA_TOTAL_COMMITMENT_AMT desc, DATA_GEN_ID desc)=1
) with data on commit preserve rows;

Please share your inputs. TIA.

Re: Convert SQL / teradata code to SAS

LinusH — Mon, 13 Feb 2023 08:33:29 GMT

S lets's break into parts:

- volatile table: it's equivalent to store a table (SAS dataset) in the WORK library.

- row_number: in SAS PROC SQL there's an undocumented (hence not supported) function monotonic(). If you need to keep track on row numbers using supported functionality you need to use the data step instead of SQL (automatic _n_ variable).

- qualify / partition by: can you explain what this does? I know about window functions but haven't really used them? Perhaps you need to attach an example of "have" data and what't the output.

Re: Convert SQL / teradata code to SAS

s_lassen — Mon, 13 Feb 2023 10:52:14 GMT

The QUALIFY statement means that we are only taking the first row from each group. I think the easiest way to do this is to take all the data out in the order we want it, and then use a data step to get the first row in each partition:

proc sql;
create table temp as
select GEN_ID, AU_GEN_ID, TYPE_2_DESC,
TYPE_5_DESC, TYPE_8_DESC,  DATA_OUTSTANDING_AMT, 
DATA_TOTAL_COMMITMENT_AMT
from COMMERCIAL_V3.DATA_F
order by GEN_ID, AU_GEN_ID, DATA_OUTSTANDING_AMT desc,
DATA_TOTAL_COMMITMENT_AMT desc, DATA_GEN_ID desc;
quit;

data want;
  set temp;
  by GEN_ID AU_GEN_ID;
  if first.AU_GEN_ID;
  drop DATA_OUTSTANDING_AMT DATA_TOTAL_COMMITMENT_AMT;
run;

This assumes that your input data has been moved to a SAS library. If it is still in Teradata, you may be better off using SQL passthrough to Teradata.

Re: Convert SQL / teradata code to SAS

Ksharp — Mon, 13 Feb 2023 11:48:31 GMT

/*
You'd better post a INPUT dataset and a OUTPUT dataset
to explain what you are looking for.
If I was right,try this code:
*/
proc sort data=have out=temp;
by 
 GEN_ID
 AU_GEN_ID 
 descending DATA_OUTSTANDING_AMT 
 descending DATA_TOTAL_COMMITMENT_AMT 
 descending DATA_GEN_ID 
;
run;
proc sort data=temp out=want nodupkey;
by 
 GEN_ID
 AU_GEN_ID 
;
run;

Re: Convert SQL / teradata code to SAS

SASKiwi — Mon, 13 Feb 2023 19:09:40 GMT

It's not clear what your actual use case is here. What data source will you be working with once you convert the Teradata code. Will it still be Teradata or will your data source change to SAS as well? If you continue to use Teradata as your data source then there is no need to rewrite your Teradata code. Just wrap it in a SAS SQL Passthru step and run it unchanged.