how to generate sequence number in sql

[Saken]

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

[Prashan]

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

[PaigeMiller]

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.

[Kurt_Bremser]

DATA step.
DATA step.
DATA step.

[Ksharp]

This topic has been discussed so many times in this forum.

If you really want it by SQL, try ODS skill.

data have;
 set SASHELP.CARS;
 keep make;
run;

ods select none;
ods output SQL_Results=SQL_Results;
proc sql number;
select * from have order by make;
quit;
ods select all;

proc sql;
create table want as
select *,row-min(row)+1 as seq from SQL_Results group by make order by row;
quit;

[mkeintz]

@Prashan wrote:

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

... stuff deleted ...

that too only with PROC SQL, not with data step, I know how to do with data step.

I think there are three statements relevant to this problem.

1. There is NO way to reliably reproduce within-group physical sequence numbers in PROC SQL using supported tools.   By "reliable" I mean reproducible with certainty. 
   
   
2. And even if you were to use monotonic(), there is no way to reproduce the results you would get in a DATA step (see my other note) unless the data were already sorted by the grouping variable.  And the use of proc sql with monotonic() would require filtering the source dataset once for each group (i.e. 38 times in the case of MAKE from sashelp.cars).  A big waste of resources.
   
   
3. But probably the most important advice is to resist the atavistic urge to use PROC SQL for a purpose that it is totally unsuited for.

[hashman]

>But probably the most important advice is to resist the atavistic urge to use PROC SQL for a purpose that it is totally unsuited for.<

Totally, Mark.
There may be some perverse utility (like developing extra flexibility) in scratching the left ear with the right foot while standing on the left leg, but scratching it with the left hand is a tad saner. Or, one might be able to perform a heart surgery with an axe and cut a tree with a scalpel, but why do it when both tools are available.
Kind regards
Paul D.

[Kurt_Bremser]

And if this is some kind of homework, tell your teacher that the purpose of a SAS course is to learn the efficient use of the SAS system, not wasting time with stupid things you'll never do in real life.

[dxiao2017]

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;

[mkeintz]

But @dxiao2017 .

The dataset example you are using is already sorted by SEX, as presented by the OP.  But that may be unlike most situations (and unlike sashelp.class which is the source of the data example, and is sorted by name, not by sex/name.).

Besides there is no need to sort the data by sex, merely to generate within-sex sequence numbers.

For instance:

data want (drop=_:);
  set sashelp.class;
  _nm+(sex='M');
  _nf+(sex='F');
  sequence=ifn(sex='M',_nm,_nf);
run;

And if one MUST use SQL, then the undocumented (read "unsupported") MONOTONIC() function (see MONOTONIC-function-in-PROC-SQL) can be used for each sex:

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

But note that data order in the case of PROC SQL will almost certainly not be the same as in the original data set.  And that the row order will actually change depending on the order of variables 

[PaigeMiller]

Many of us advise against using undocumented features such as MONOTONIC because of the reasons stated by @mkeintz and also because when the next release of SAS comes out, the same code may provide different results, or it may not even work. In fact, even within the same release of SAS, it might work properly on data set A and not work correctly on data set B. So you use it at your own risk.

Furthermore, I'm sure that if any auditor of my code (I work at a bank where code can be audited by either internal or external auditors), using undocumented features of a computer language would cause my code to fail the audit.