topic Re: SAS replacement for WITH AS (Oracle SQL clause)? in SAS Programming

SAS replacement for WITH AS (Oracle SQL clause)?

MartinJurca — Sun, 26 Jan 2020 18:33:32 GMT

Hi.
Is there a replacement in SAS SQL for an Oracle SQL WITH AS function?

Or do I have to use CASE or something like that in SAS to get the same results?

How it looks like in Oracle:

with income as (
select 'Porsche' as Brand, 40000 as Income, date'2017-01-01' as Valid_From, date'2999-12-31' as Valid_To from dual union all
select 'Ferrari' as Brand, 80000 as Income, date'2017-01-01' as Valid_From, date'2999-12-31' as Valid_To from dual union all
select 'Lamborghini' as Brand, 50000 as Income, date'2017-01-01' as Valid_From, date'2019-12-31' as Valid_To from dual union all
select 'Lamborghini' as Brand, 75000 as Income, date'2020-01-01' as Valid_From, date'2999-12-31' as Valid_To from dual union all
)

Re: SAS replacement for WITH AS (Oracle SQL clause)?

Reeza — Sun, 26 Jan 2020 18:54:26 GMT

proc sql;
create table want as
select * from (select 'Porsche' as Brand, 40000 as Income, '01Jan2017'd as Valid_From, '31Dec2999'd as Valid_To from sashelp.class(obs=1) union all
select 'Ferrari' as Brand, 80000 as Income, '01Jan2017'd as Valid_From, '31Dec2999'd as Valid_To from sashelp.class(obs=1) union all
select 'Lamborghini' as Brand, 50000 as Income, '01Jan2017'd as Valid_From, '31Dec2019'd as Valid_To from sashelp.class (obs=1) union all
select 'Lamborghini' as Brand, 75000 as Income, '01Jan2020'd as Valid_From, '31Dec2999'd as Valid_To from sashelp.class(obs=1))
; 
quit; 

data want;
infile cards dlm=',';
informat Brand $15. Valid_From Valid_To date9.;
format Valid_From Valid_To date9.;
input Brand Income Valid_From Valid_To;
cards;
Porsche, 40000, '01Jan2017', '31Dec2999'
Ferrari, 80000, '01Jan2017', '31Dec2999'
Lamborghini, 50000, '01Jan2017', '31Dec2019'
Porsche, 4000, '01Jan2020', '31Dec2999'
;;;;
run;

It looks like you're creating a table here with hard coded data? There's a few ways to do that in SAS. Do you need it inline, or as a subquery or can it be a separate step all together?

Depending on what you're doing overall there may be other ways, especially if this is a look up table.

@MartinJurca wrote:

Hi.
Is there a replacement in SAS SQL for an Oracle SQL WITH AS function?

Or do I have to use CASE or something like that in SAS to get the same results?

How it looks like in Oracle:

with income as (
select 'Porsche' as Brand, 40000 as Income, date'2017-01-01' as Valid_From, date'2999-12-31' as Valid_To from dual union all
select 'Ferrari' as Brand, 80000 as Income, date'2017-01-01' as Valid_From, date'2999-12-31' as Valid_To from dual union all
select 'Lamborghini' as Brand, 50000 as Income, date'2017-01-01' as Valid_From, date'2019-12-31' as Valid_To from dual union all
select 'Lamborghini' as Brand, 75000 as Income, date'2020-01-01' as Valid_From, date'2999-12-31' as Valid_To from dual union all
)

Re: SAS replacement for WITH AS (Oracle SQL clause)?

Tom — Sun, 26 Jan 2020 21:10:27 GMT

To me that looks like two different questions. The direct answer is that SAS does no support Common Table Expressions. https://modern-sql.com/feature/with

You could either just run a separate step to create the result as a table (or view) and then reference it in your query. Or just code it directly into your query as a subquery.

But the second question is how to get the query you wrote that uses the Oracle system psuedo table DUAL to work in SAS. The easiest thing is to just use a data step to read the data as text.

data income;
  infile cards dsd truncover ;
  input Brand :$20. Income Valid_From :yymmdd. Valid_To :yymmdd. ;
  format Valid_From Valid_To yymmdd10.;
cards;
Porsche   , 40000, 2017-01-01,2999-12-31
Ferrari   , 80000, 2017-01-01,2999-12-31
Lamborghin, 50000, 2017-01-01,2019-12-31
Lamborghin, 75000, 2020-01-01,2999-12-31
;

Note you could trick SAS into doing something similar to DUAL by just selecting one observation from some dataset you know exists, like SASHELP.CLASS.

select 'Porsche' as Brand
     , 40000 as Income
     , '01JAN2017'd as Valid_From format=yymmdd10.
     , '31DEC2999'd as Valid_To format=yymmdd10.
  from sashelp.class(obs=1)