topic Re: proc sql : first and last in SAS Programming

proc sql : first and last

SASdevAnneMarie — Tue, 05 Apr 2022 13:16:40 GMT

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 !

Re: proc sql : first and last

Ksharp — Tue, 05 Apr 2022 13:25:25 GMT

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;

Re: proc sql : first and last

Kurt_Bremser — Tue, 05 Apr 2022 13:45:13 GMT

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.

Re: proc sql : first and last

Sajid01 — Tue, 05 Apr 2022 14:57:42 GMT

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

Re: proc sql : first and last

Kurt_Bremser — Tue, 05 Apr 2022 16:08:11 GMT

@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.