sasproc-sql

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

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

enter image description here

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.

Solution

  • 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