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

[alepage]

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

[PaigeMiller]

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.

[LinusH]

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 🙂

[Kurt_Bremser · Accepted solution]

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;

[alepage]

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.

[Tom]

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.

[Ksharp]

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;