proc sql : first and last

[SASdevAnneMarie]

Hello Experts,

I'm wondering how to translate this code in proc sql : is it possible to write "first" function on proc sql ? 

data table2;
	set table1;
	by prod lb_lg;
	retain N;

	if first.prod then
		do;
			N = 1;
			output;
		end;
	else
		do;
			N = N + 1;
			output;
		end;
run

 Thank you for your help !

[Ksharp]

ods select none;
ods output sql_results=sql;
proc sql number;
select * from sashelp.class order by sex,age;
quit;
ods select all;

proc sql;
create table want as
select *,row-min(row)+1 as n from sql
 group by sex,age
  order by row;
quit;

[Kurt_Bremser]

Basically no, because SQL thinks in sets (order irrelevant) and not in a sequence of rows.

As already shown, you can do some tricks, but the data step will outperform them by orders of magnitude.

And you can simplify your data step code, which also improves readability/maintainability:

data table2;
set table1;
by prod lb_lg;
if first.prod
then N = 1;
else N + 1;
run

The SUM statement implies an automatic retain, and since you had OUTPUT in both branches of the IF, you can use the implicit output of the data step.

[Sajid01]

Hello @SASdevAnneMarie 
This can be done indirectly if the idea is to use Proc Sql. I am showing an example with sashelp.class as follows.

proc sql;

create table test as
select *
  from
(select monotonic() as N,* from sashelp.class)
quit;

The first few rows of the output will be as follows

[Kurt_Bremser]

@SASdevAnneMarie but one must always be advised that the working of the (undocumented!) function MONOTONIC is not guaranteed. Depending on the data source, it may not create the expected result.