how to generate sequence number in sql

[thanikondharish]

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

[SuryaKiran]

Use monotonic() function 

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

[thanikondharish]

i want group wise (sex)

[PeterClemmensen]

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

[hashman]

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.   

[andreas_lds]

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

[DrAbhijeetSafai]

@SuryaKiran ,

Wonderful! Thanks a lot!

Thank you.

- Dr. Abhijeet Safai

[Kurt_Bremser]

Maxim 14. Data step.

[Andygray]

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

[novinosrin]

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;

[novinosrin]

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;

[hashman]

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

[novinosrin]

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. 

[Allaluiah]

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

[PGStats]

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.