topic Re: sequence number in SAS Procedures

sequence number

raveena — Tue, 21 Jun 2011 17:03:14 GMT

Hi,

I need to generate an automated sequence number in the report.I have tried with monotonic options in proc sql and _n_ in data step...But am not getting an exact output.

SOURCE TABLE
ID________COMMENT
123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket

Output
ID_____SEQ______COMMENT
123_____1_______I am joe
123_____2_______I am programmer
124_____1_______I am wang
124_____2_______I am programmer
124_____3_______I like cricket

Can somebody please advise the way to do this in proc sql?

Thanks in Advance!

Re: sequence number

Doc_Duke — Tue, 21 Jun 2011 18:46:44 GMT

Raveena,

You probably need to do this in a DATA step. SQL doesn't generally recognize order with groups of an ORDER BY clause. RETAIN and FIRST are the concepts you need to read about to do this in a DATA step:

DATA out;
SET source;
BY id;
RETAIN seq;
IF first.id THEN seq=0;
seq+1;
RUN;

Doc Muhlbaier
Duke

Re: sequence number

raveena — Tue, 21 Jun 2011 19:17:07 GMT

Thanks Duke !!