SAS Data Set Setting Variables of data set to add line numbers

Question

I have the following dataset

Id	com	typ	cust	bu	tar	item	item_sufx	part	line	dtn_cd
10	ARF	3	2585	12	100	4587	800	1	1	1
10	ARF	3	2585	12	100	4587	800	1	2	1
10	ARF	3	2585	12	100	4587	800	1	3	2
22	XYZ	3	2585	12	100	4587	800	1	1	1
22	XYZ	3	2585	12	100	4587	800	1	2	2
22	XYZ	3	2585	12	100	4587	800	1	3	2
10	ARF	3	2585	12	100	4587	800	2	1	1
10	ARF	3	2585	12	100	4587	800	2	2	2
10	ARF	3	2585	12	100	4587	800	3	1	3
10	ARF	3	2585	12	100	4587	800	3	2	4
50	PFP	3	3000	12	100	9999	899	1	1	1
50	PFP	3	3000	12	100	9999	899	1	2	2
50	PFP	3	3000	12	100	9999	899	1	1	3
50	PFP	3	3000	12	100	9999	899	1	2	4

I want the following output

I tried the following code but it didn't work properly

data table_A_mod;
set table_A;
by Id com typ cust bu tar item item_sufx part dtn_cd;
line_decreased = line_nbr < lag(line_nbr);
if first.part 
or line_decreased
then line_group+1;
if first.dtn_cd then drctn_seq=1; else drctn_seq+1;
run;

I get the following error

ERROR: BY variables are not properly sorted on data set WORK.Table_A.

I tried many combinations of "by" for the variables but nothing worked.

I have built up on my previous question here stackoverflow.com/questions/77100373/merge-group-data-in-sas but the difference between the two is in the current question I have different ID and Com values in the same item_sufx group. This is why my code isn't working.

Any idea how do that? I am open to SAS or PROC SQL (DB2) suggestions.

Answer 1

If you want the data sorted then sort it.

proc sort data=have;
  by Id com typ cust bu tar item item_sufx part dtn_cd;
run;

data want ;
  set have;
  by Id com typ cust bu tar item item_sufx part dtn_cd;
  if first.id or line<lag(line) then group+1;
run;

Result

                                                        item_
Obs    id    com    typ    cust    bu    tar    item     sufx    part    line    dtn_cd    group

  1    10    ARF     3     2585    12    100    4587     800       1       1        1        1
  2    10    ARF     3     2585    12    100    4587     800       1       2        1        1
  3    10    ARF     3     2585    12    100    4587     800       1       3        2        1
  4    10    ARF     3     2585    12    100    4587     800       2       1        1        2
  5    10    ARF     3     2585    12    100    4587     800       2       2        2        2
  6    10    ARF     3     2585    12    100    4587     800       3       1        3        3
  7    10    ARF     3     2585    12    100    4587     800       3       2        4        3
  8    22    XYZ     3     2585    12    100    4587     800       1       1        1        4
  9    22    XYZ     3     2585    12    100    4587     800       1       2        2        4
 10    22    XYZ     3     2585    12    100    4587     800       1       3        2        4
 11    50    PFP     3     3000    12    100    9999     899       1       1        1        5
 12    50    PFP     3     3000    12    100    9999     899       1       2        2        5
 13    50    PFP     3     3000    12    100    9999     899       1       1        3        6
 14    50    PFP     3     3000    12    100    9999     899       1       2        4        6