BookmarkSubscribeRSS Feed
thanikondharish
Calcite | Level 5

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

15 REPLIES 15
SuryaKiran
Meteorite | Level 14

Use monotonic() function 

 


proc sql;
select *,monotonic() as row_no
from sashelp.class;
quit;
Thanks,
Suryakiran
thanikondharish
Calcite | Level 5
i want group wise (sex)
PeterClemmensen
Tourmaline | Level 20

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

hashman
Ammonite | Level 13

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
Jade | Level 19

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

DrAbhijeetSafai
Pyrite | Level 9

@SuryaKiran ,

 

Wonderful! Thanks a lot!

 

Thank you.

 

- Dr. Abhijeet Safai

 

Dr. Abhijeet Safai
Associate Data Analyst
Actu-Real
Andygray
Quartz | Level 8

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
Tourmaline | Level 20

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
Tourmaline | Level 20
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
Ammonite | Level 13

@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
Tourmaline | Level 20

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
Quartz | Level 8

@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
Opal | Level 21

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.

PG

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 17488 views
  • 22 likes
  • 12 in conversation