topic assigning sequence when values are not sorted in SAS Programming

assigning sequence when values are not sorted

paddyb — Tue, 03 Apr 2018 19:31:26 GMT

I have to assign seq for the listed paramcd.Paramcd is not sorted .Any idea how I can apply these seq?

Thanks.

Re: assigning sequence when values are not sorted

Reeza — Tue, 03 Apr 2018 19:43:33 GMT

Same idea as if it was sorted but use NOTSORTED option on the BY statement.

Please include your data directly into your post, I don't want to download attachments, write a program to read the file and then figure out the answer to your question. If you do the first two steps, I'm much more likely to do the last one.

data want;
set sashelp.class;
by sex NOTSORTED;

if first.sex then count+1;

run;

Re: assigning sequence when values are not sorted

novinosrin — Tue, 03 Apr 2018 20:12:38 GMT

Are you just trying to create a obs incremental count as your attachment suggests?

data have;
input SEQ       PARAMCD : $10.;
drop seq;
datalines;
1	AR13MBG
2	AR14MBG
3	AR15MBG
4	AR16MBG
5	AR17MBG
6	AR18MBG
7	AR19MBG
8	AR20MBG
9	AR21MBG
10	AR22MBG
11	AR23MBG
12	AR24MBG
13	AR25MBG
14	AR26MBG
15	AR27MBG
16	AR28MBG
17	MBGGR
18	AR11LSD
19	AR22LSD
20	AR33LSD
21	AR49LSD
22	AR34LSD
23	AR35LSD
24	AR41LSD
25	AR42LSD
26	AR43LSD
27	AR44LSD
28	AR45LSD
29	AR46LSD
30	AR47LSD
31	AR48LSD
32	LSDGR
33	AR01PCAG
34	AR02PCAG
35	AR03PCAG
36	AR04PCAG
37	AR05PCAG
38	AR06PCAG
39	AR07PCAG
40	AR08PCAG
41	AR09PCAG
;
data want;
set have;
seq+1;
run;

Re: assigning sequence when values are not sorted

SuryaKiran — Tue, 03 Apr 2018 20:55:32 GMT

You can also use MONOTONIC() function in PROC SQL.

proc sql;
select monotonic() as SEQ, ID 
from have;
quit;

Re: assigning sequence when values are not sorted

Reeza — Tue, 03 Apr 2018 20:56:57 GMT

If it's a row number use _n_.

data want;
set sashelp.class;

row_number = _n_;

run;

Re: assigning sequence when values are not sorted

paddyb — Tue, 03 Apr 2018 21:16:27 GMT

Apologies ,I think I didnt frame question correctly.Actually paramcd is not sorted means ,in data ,its not in above order.I dont want to use proc format as there are 100's of values.data I have is in below order and dataset I want in like above in original post.

Re: assigning sequence when values are not sorted

Reeza — Tue, 03 Apr 2018 21:20:00 GMT

This is still unclear.

Where does proc format come into play. Post your data into the forum directly please, not as an attachment.

Re: assigning sequence when values are not sorted

Kurt_Bremser — Wed, 04 Apr 2018 07:59:18 GMT

Nothing easier than creating a format from data:

data have;
input paramcd $;
cards;
AGGR
AR01PCAG
AR02PCAG
AR03PCAG
AR04PCAG
AR05PCAG
AR06PCAG
AR07PCAG
AR08PCAG
AR09PCAG
AR10BG
AR10PCAG
AR11LSD
AR11PCAG
AR12PCAG
AR13MBG
AR14MBG
AR15MBG
AR16MBG
AR17MBG
AR18MBG
AR19MBG
AR20MBG
AR21MBG
AR22LSD
AR22MBG
AR23BG
AR23MBG
AR23PCAG
AR24AG
AR24MBG
AR25AG
AR25MBG
AR26AG
AR26BG
AR26MBG
AR27AG
AR27MBG
AR28BG
AR28MBG
AR29AG
AR29PCAG
AR30AG
AR30BG
AR31AG
AR31BG
AR32AG
AR32BG
AR33AG
AR33LSD
AR34AG
AR34BG
AR34LSD
AR35AG
AR35LSD
AR36BG
AR37BG
AR38BG
AR39BG
AR40BG
AR41LSD
AR42LSD
AR43LSD
AR44LSD
AR45LSD
AR46LSD
AR47LSD
AR48LSD
AR48PCAG
AR49LSD
BGGR
LSDGR
MBGGR
PCAGG
;
run;

data cntlin;
set have;
rename paramcd=start;
label = put(_n_,best.);
type = 'C';
fmtname = 'paramcd';
run;

proc format library=work cntlin=cntlin;
run;

data want;
set have;
wantval = put(paramcd,$paramcd.);
run;

proc print data=want noobs;
run;

Result (partial):

paramcd     want

AGGR           1
AR01PCAG       2
AR02PCAG       3
AR03PCAG       4
AR04PCAG       5
AR05PCAG       6
AR06PCAG       7
AR07PCAG       8
AR08PCAG       9
AR09PCAG      10
AR10BG        11
AR10PCAG      12
AR11LSD       13
AR11PCAG      14
AR12PCAG      15
AR13MBG       16
AR14MBG       17
AR15MBG       18
AR16MBG       19
AR17MBG       20

Re: assigning sequence when values are not sorted

paddyb — Wed, 04 Apr 2018 16:37:15 GMT

Thanks everyone.Actually these paramcd were derived in separately and then setting underneath each other I was trying to derive seq.

So option that worked is,I derived seq when I derived parameter separately and then set these datasets.