topic Re: How to change over partition and with table as statement in a SQL code in SAS Enterprise Guide

How to change over partition and with table as statement in a SQL code

alepage — Thu, 27 Jun 2024 12:51:21 GMT

Hello,

I dont think that SAS recognize the SQL statement with table as neither over partition. Which changes do I need to do to work with SAS

with 

dates as (
   select  date_trunc('MONTH', current_date)-1 as  PIT_day,
   to_timestamp(to_char(date_trunc('MONTH', current_date)-3,'YYYY-MM-DD') || ' 20:30:00','YYYY-MM-DD HH24:MI:SS')  as AUTH_DT
)

select row_number() over (partition by MAIN_INSRR_CO_NBR, pol_typ_cd,pol_nbr order by TRM_NBR desc, TRM_VER_NBR desc) as NO, 
 aa.*
 from  table_1 aa

Re: How to change over partition and with table as statement in a SQL code

PaigeMiller — Thu, 27 Jun 2024 12:54:18 GMT

I don't understand the question, your description is far too brief.

Please show us the complete PROC SQL for this problem, and provide sample data and desired output.

Re: How to change over partition and with table as statement in a SQL code

LinusH — Thu, 27 Jun 2024 13:02:18 GMT

That's correct, SAS doesn't support windowing functions (which has a SAS ballot entry for btw, feel free to vore 🙂 Add window functions in SAS SQL - SAS Support Communities)

If your data resides in adatabase that support these functions, you could use explict SQL pass.through.

If you need to use SAS syntax still, please provide a have example data set (using datalines) and a want output so we don't have dry interpret your SQL 🙂

Re: How to change over partition and with table as statement in a SQL code

Kurt_Bremser — Thu, 27 Jun 2024 13:04:44 GMT

The second one might look like this:

data want;
set table1;
by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr;
if first.pol_nbr
then no = 1;
else no + 1;
run;

Re: How to change over partition and with table as statement in a SQL code

Tom — Thu, 27 Jun 2024 15:36:18 GMT

It would help a lot if you explained what the code you shared is supposed to be doing.

But the simple answer is to NOT use SQL. Just write it in plain old SAS code.

To replicate a WITH clause either make the dataset before hand. Or define a view that will make it on demand. But your current WITH clause does not appear to have any FROM clause, so it is not clear to me how that is valid SQL. If you just want to make constant values then there is no need to make a dataset for that.

To manipulate date values use INTNX() function.

Looks like you might be wanting to do something like this. But it is not clear why you want one of the value to be a DATE value (number of days) and the other to be a DATETIME value (number of seconds).

PIT_day = intnx('month',date(),-2,'e');
AUTH_DT = intnx('dtmonth',datetime(),-3,'b')+'20:30:00't ;
format pit_day date9. auth_dt datetime19.;

To number observations use a SUM statement.

It looks like you are trying number the observations within BY groups. Not clear why you want the new variable as the first variable in the dataset but this should do it.

data WANT;
   NO + 1;
   set table_1;
   by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr descending TRM_NBR descending TRM_VER_NBR;
   if first.pol_nbr then NO=1;
run;

Note that if the data is not already sorted then SAS will fail this step so you might need to first run PROC SORT with the same BY statement to get the data in the needed order.

Re: How to change over partition and with table as statement in a SQL code

Ksharp — Fri, 28 Jun 2024 02:28:10 GMT

If you really like to use SQL ,you could try this one:

ods select none;
proc sql number;
ods output sql_results=temp;
select * from sashelp.class order by sex;



create table want as
select *,row-min(row)+1 as seq from temp group by sex order by row;
quit;
ods select all;

Re: How to change over partition and with table as statement in a SQL code

alepage — Thu, 18 Jul 2024 13:17:20 GMT

Good morning,

Thanks for the SAS script equivalent to partition over.

How do we add the instructions order by TRM_NBR desc, TRM_VER_NBR desc. Do we add it into the by statement or is it better to do a proc sort by MAIN_INSRR_CO_NBR, pol_typ_cd,pol_nbr order by TRM_NBR desc, TRM_VER_NBR desc.