topic Re: how to generate sequence number in sql in SAS Programming

how to generate sequence number in sql

thanikondharish — Tue, 22 Jan 2019 15:32:25 GMT

Name	Sex	Age	Height	Weight	seq
Alice	F	13	56.5	84	1
Barbara	F	13	65.3	98	2
Carol	F	14	62.8	102.5	3
Jane	F	12	59.8	84.5	4
Janet	F	15	62.5	112.5	5
Joyce	F	11	51.3	50.5	6
Judy	F	14	64.3	90	7
Louise	F	12	56.3	77	8
Mary	F	15	66.5	112	9
Alfred	M	14	69	112.5	1
Henry	M	14	63.5	102.5	2
James	M	12	57.3	83	3
Jeffrey	M	13	62.5	84	4
John	M	12	59	99.5	5
Philip	M	16	72	150	6
Robert	M	12	64.8	128	7
Ronald	M	15	67	133	8
Thomas	M	11	57.5	85	9
William	M	15	66.5	112	10

how to generate seq number in sql sex group wise

Re: how to generate sequence number in sql

SuryaKiran — Tue, 22 Jan 2019 15:36:35 GMT

Use monotonic() function


proc sql;
select *,monotonic() as row_no
from sashelp.class;
quit;

Re: how to generate sequence number in sql

thanikondharish — Tue, 22 Jan 2019 15:39:15 GMT

i want group wise (sex)

Re: how to generate sequence number in sql

PeterClemmensen — Tue, 22 Jan 2019 15:44:01 GMT

Why do you want to do this is SQL? So much easier in a data step with first. and last. logic

Re: how to generate sequence number in sql

Kurt_Bremser — Tue, 22 Jan 2019 15:46:22 GMT

Maxim 14. Data step.

Re: how to generate sequence number in sql

Andygray — Tue, 22 Jan 2019 15:50:58 GMT

I totally concur with @Kurt_Bremser , @PeterClemmensen . Why? why? and why?

what is that you are going to achieve?

Whomsoever gave you that idea sounds like somebody who doesn't know SAS programming at all

Re: how to generate sequence number in sql

andreas_lds — Tue, 22 Jan 2019 15:52:45 GMT

Please use the right tool, a data-step in this case, or explain why you want to use proc sql.

Re: how to generate sequence number in sql

novinosrin — Tue, 22 Jan 2019 16:11:01 GMT

Weird question if an organisation is spending money for enterprise sas licence and not utilising sas. hmm well

proc sql;
create table want as
select *,monotonic() as seq
from sashelp.class
where sex='F'
union
select *,monotonic() as seq
from sashelp.class
where sex='M'
order by sex,seq;
quit;

Re: how to generate sequence number in sql

novinosrin — Tue, 22 Jan 2019 16:58:23 GMT

proc sql;
create table t as
select *,monotonic() as m 
from sashelp.class
order by m;/*Strict ordering required to source*/

create table want(drop=m:) as
select distinct a.*,sum(s) as seq
from t a,(select sex,monotonic() as m2, 1 as s from sashelp.class ) b
where a.sex=b.sex and m2<=m
group by a.sex,m
order by a.sex,seq;
quit;

Re: how to generate sequence number in sql

Allaluiah — Tue, 22 Jan 2019 18:26:39 GMT

@thanikondharish For whatever reason, you don't seem to acknowledge answers or appreciate the solutions that is apparent having been noticed in many of your posts/questions. It's so strange and difficult to comprehend why anyone would lack that very basic courtesy to at least thank and value the time of the person who has responded.

I am absolutely certain many others have noticed the same and have let you know of this rather not pleasing demeanor. I truly believe such ethics doesn't have to be taught or spoon fed. If I am not wrong, @VDD had mentioned this too in another thread.

It's seriously disheartening to the ones who are ethical, be it who post questions or solutions.

Re: how to generate sequence number in sql

PGStats — Tue, 22 Jan 2019 18:57:09 GMT

If your table includes a unique key (such as Name in the case of sashelp.class):

proc sql;
select 
    a.*,
    (select count(*) from sashelp.class where sex=a.sex and name <= a.name) as seq
from sashelp.class as a
order by sex, name;
quit;

Note: This is NOT efficient. Do not try this on large datasets. Monotonic() is more efficient, but is not an officially supported feature of SAS/SQL or guaranteed to work in all situations.

Re: how to generate sequence number in sql

hashman — Tue, 22 Jan 2019 21:48:21 GMT

S/he might not want to do it in SQL; but might be in a situation where a person in a position of say-so authority demands that it be done using SQL only no matter what - in particular if her/his own knowledge of SAS is confined to SQL (which I've seen more than once). To a hammer, everything had better look like a nail.

Of course, it goes both ways, and in days of yore, I've seen SAS programmers stubbornly refusing to use SQL even when it was the best tool for the job and SAS SQL already was long past maturity.

Paul D.

Re: how to generate sequence number in sql

hashman — Tue, 22 Jan 2019 22:04:22 GMT

@novinosrin:

You show another time why in this case SQL is an awkward tool for the job, as it is any time when processing has to deal with physical sequences of records. In this case, it's even more true since it can be done in a DATA step without sorting the data by sex beforehand:

data want ;                      
  if _n_ = 1 then do ;           
    dcl hash h () ;              
    h.defineKey ("sex") ;        
    h.defineData ("seq") ;       
    h.defineDone () ;            
  end ;                          
  set sashelp.class ;            
  if h.find() ne 0 then seq = 1 ;
  else                  seq + 1 ;
  h.replace() ;                  
run ;

Best

Paul D.

Re: how to generate sequence number in sql

novinosrin — Tue, 22 Jan 2019 22:15:26 GMT

Guru @hashman , totally agree. I get the feeling OP isn't from SAS background as noted in your 1st post in this thread response to draycut. To add to that, the trend seems to be OPs from DB background perhaps pl/sql etc want us to help them using sql while the BI/Analytics/ more so fancy data science folks want us to provide SAS solutions.

Oh well, you know it all and too well.

PS Hash without sort is indeed slick.

Annexure:

Wouldn;t you require a sort from the the hash output? I suppose that's assumed for the OP.

Re: how to generate sequence number in sql

Saken — Sat, 09 Jan 2021 09:42:06 GMT

data TABLE;
set WORK.TABLENAME;
by Sex;
if first.Sex then SEQUENCE = 1;
else SEQUENCE + 1;
run; Quit;

Re: how to generate sequence number in sql

DrAbhijeetSafai — Tue, 19 Mar 2024 09:27:27 GMT

@SuryaKiran ,

Wonderful! Thanks a lot!

Thank you.

- Dr. Abhijeet Safai

Re: how to generate sequence number in sql

Prashan — Thu, 29 May 2025 10:20:23 GMT

Consider SASHELP.CARS dataset and give the sequence number for MAKE variable.
Ex:- in MAKE variable, I want sequence number for AUDI

Below is my required Output, that too only with PROC SQL, not with data step, I know how to do with data step.

MAKE SEQ
--------------
AUDI 1
AUDI 2
AUDI 3
BMW 1
BMW 2
BMW 3

Re: how to generate sequence number in sql

PaigeMiller — Thu, 29 May 2025 10:26:02 GMT

As many people in this thread have already said, you have chosen the wrong tool. SQL is not the right tool, and requesting that you want code to do it in SQL doesn't change the fact that SQL is not the right tool.

Re: how to generate sequence number in sql

Kurt_Bremser — Thu, 29 May 2025 11:22:06 GMT

DATA step.
DATA step.
DATA step.

Re: how to generate sequence number in sql

dxiao2017 — Thu, 29 May 2025 15:36:26 GMT

Hi @thanikondharish , this is a solution using data step (not sql) base on the codes, suggestions, and comments from others in previous threads:

data hw;
   input Name $ Sex $ Age Height Weight;
   datalines;
Alice F 13 56.5 84
Barbara F 13 65.3 98
Carol F 14 62.8 102.5
Jane F 12 59.8 84.5
Janet F 15 62.5 112.5
Joyce F 11 51.3 50.5
Judy F 14 64.3 90
Louise F 12 56.3 77
Mary F 15 66.5 112
Alfred M 14 69 112.5
Henry M 14 63.5 102.5
James M 12 57.3 83
Jeffrey M 13 62.5 84
John M 12 59 99.5
Philip M 16 72 150
Robert M 12 64.8 128
Ronald M 15 67 133
Thomas M 11 57.5 85
William M 15 66.5 112
;
run;
proc print data=hw;run;
data hwseq;
   set hw;
   by sex;
   seq+1;
   if first.sex then seq=1;
run;
proc print data=hwseq;run;