Page Number counter and reset

[PROCDATARUN]

Hello Programming Nerds.

Help out here again with page numbers assignment.

Explanation: Input dataset has order details where the same set of Tracknumbers are repeated across SetNumbers for a given Order number.

The end goal is :

Assign page number starting 1 to 10 for a unique Tracknumber for a given order number and set number.
Do not assign any page number after 10 hits for the given Set Number or exclude them starting 11th tracknumber for the given order and set number. 
On the same Order Number a second Set number would have the same set of first 10 uniique track numbers which should be assigned no page number or excluded. 
Page numbering should start for the 11th unique tracknumber starting 1 through 10,which again should stop after 10 unique track numbers meaning 20th unique track number for all of the Order Number.
This iteration should allow 100 unique track numbers, meaning 10 page sets for 10 set numbers.

See sample input and output dataset below, your help is truly appreciated as always.

Input							Output
OrderNumber	SetNumber	TrackNumber	SubGNumber		OrderNumber	SetNumber	TrackNumber	SubGNumber	PageNumber
O123	J001	456Y	A		O123	J001	456Y	A	1
O123	J001	456Y	B		O123	J001	456Y	B	1
O123	J001	456Y	C		O123	J001	456Y	C	1
O123	J001	3284G	A		O123	J001	3284G	A	2
O123	J001	3284G	B		O123	J001	3284G	B	2
O123	J001	5657G	A		O123	J001	5657G	A	3
O123	J001	490456B	B		O123	J001	490456B	B	4
O123	J001	490456C	C		O123	J001	490456C	C	5
O123	J001	490456D	A		O123	J001	490456D	A	6
O123	J001	490E	B		O123	J001	490E	B	7
O123	J001	490F	A		O123	J001	490F	A	8
O123	J001	490G	B		O123	J001	490G	B	9
O123	J001	490H	C		O123	J001	490H	C	10
O123	J001	490I	A		O123	J002	490I	A	1
O123	J001	490J	B		O123	J002	490J	B	2
O123	J002	456Y	A		O346	J003	456Y	A	1
O123	J002	456Y	B		O346	J003	456Y	B	1
O123	J002	456Y	C		O346	J003	456Y	C	1
O123	J002	3284G	A		O346	J003	3284G	A	2
O123	J002	3284G	B		O346	J003	3284G	B	2
O123	J002	5657G	A
O123	J002	490456B	B
O123	J002	490456C	C
O123	J002	490456D	A
O123	J002	490E	B
O123	J002	490F	A
O123	J002	490G	B
O123	J002	490H	C
O123	J002	490I	A
O123	J002	490J	B
O346	J001	456Y	A
O346	J001	456Y	B
O346	J001	456Y	C
O346	J001	3284G	A
O346	J001	3284G	B

[RichardDeVen]

You will need to track TRACKs in an order, and unique TRACKs in a set.

- A list of TRACKs in an order can be maintained in a HASH

- The unique TRACKS in the set do not need to be explicitly tracked, only how many TRACKs within the set have been added to the list.

- Only the rows with a track added 1st to 10th item in the list for a SET are output

Example:

Data

Spoiler

data have;
input
Order: $4. Set: $4. Track: $7. SubGroup: $1.;
datalines;
O123	J001	456Y	A
O123	J001	456Y	B
O123	J001	456Y	C
O123	J001	3284G	A
O123	J001	3284G	B
O123	J001	5657G	A
O123	J001	490456B	B
O123	J001	490456C	C
O123	J001	490456D	A
O123	J001	490E	B
O123	J001	490F	A
O123	J001	490G	B
O123	J001	490H	C
O123	J001	490I	A
O123	J001	490J	B
O123	J002	456Y	A
O123	J002	456Y	B
O123	J002	456Y	C
O123	J002	3284G	A
O123	J002	3284G	B
O123	J002	5657G	A
O123	J002	490456B	B
O123	J002	490456C	C
O123	J002	490456D	A
O123	J002	490E	B
O123	J002	490F	A
O123	J002	490G	B
O123	J002	490H	C
O123	J002	490I	A
O123	J002	490J	B
O346	J001	456Y	A
O346	J001	456Y	B
O346	J001	456Y	C
O346	J001	3284G	A
O346	J001	3284G	B
;

Program

data want;
  set have;
  by order set;

  attrib 
    Page format=4.
  ;

  if _n_ = 1 then do;
    FromSet = Set;
    declare hash tracks();
    tracks.defineKey('Track');
    tracks.defineData('Page');
    tracks.defineData('FromSet');
    tracks.defineDone();
  end;

  if first.order then
    tracks.Clear();

  if first.set then
    seq_TrackInSet = 0;

  if tracks.find() ne 0 then do;  /* new track in set */
    seq_TrackInSet + 1;
    if seq_TrackInSet <= 10 then do;
      Page = seq_TrackInSet;
      FromSet = Set;
      tracks.add();
    end;
  end;

  * for edification, flag the wanted row;
  if 1 <= seq_TrackInSet <= 10 and Set=FromSet then want_row = '*';
run;

Your data does not show the case of <10 unique tracks in the first set and your rules "Page numbering should start for the 11th unique track number starting 1 through 10" are a bit unclear.

If the first SET of an ORDER had 8 unique tracks, would the first two unique tracks in the second SET of the same ORDER be Page 1 and 2, or Page 9 & 10 ?

[PROCDATARUN]

Thanks a billion Richard for your help so far.

For an order number, there will only be one set number if there are 10 or less track numbers.

Lets say the order has 55 track numbers, there would be 6 set numbers but all 55 track numbers will be associated with all 6 set numbers. 

The need is to assign first 10 track numbers to set 1 ( page 1 - 10 ) , second set of 10 track numbers ( 11-20 ) to set 2 (page 1 - 10) and so on allowing upto 100 track numbers which turn out to 10 pages each for 10 different set numbers.

Did this add any clarity or now ? Please let me know if it hasn't or still have any other question.

__If the first SET of an ORDER had 8 unique tracks, would the first two unique tracks in the second SET of the same ORDER be Page 1 and 2, or Page 9 & 10 ?

If fist set has only 8 unique tracks, there wouldn't be a second set number for that Order number.

[RichardDeVen]

I think you should be good with the code posted.

The 'want_row' is for examining algorithm operation and can be used, after tests prove it reliable, to restrict output.

Further restrict to first 100 unique tracks (in what I presume to be in >= 10 sets ?) with following 

if tracks.num_items <= 100 and 1 <= seq_TrackInSet <= 10 and Set=FromSet then want_row = '*';

if want_row = '*';   *subsetting if;

drop want_row seq_TrackInSet;

[PROCDATARUN · Accepted solution]

Thanks Richard for your continued support. 

Running into the below error, could you help get to the finish line here.

ERROR 180-322: Statement is not valid or it is used out of proper order.

data have;
input
Order: $4. Set: $4. Track: $7. SubGroup: $1.;
datalines;
O123 J001 456Y A
O123 J001 456Y B
O123 J001 456Y C
O123 J001 3284G A
O123 J001 3284G B
O123 J001 5657G A
O123 J001 490456B B
O123 J001 490456C C
O123 J001 490456D A
O123 J001 490E B
O123 J001 490F A
O123 J001 490G B
O123 J001 490H C
O123 J001 490I A
O123 J001 490J B
O123 J002 456Y A
O123 J002 456Y B
O123 J002 456Y C
O123 J002 3284G A
O123 J002 3284G B
O123 J002 5657G A
O123 J002 490456B B
O123 J002 490456C C
O123 J002 490456D A
O123 J002 490E B
O123 J002 490F A
O123 J002 490G B
O123 J002 490H C
O123 J002 490I A
O123 J002 490J B
O346 J001 456Y A
O346 J001 456Y B
O346 J001 456Y C
O346 J001 3284G A
O346 J001 3284G B
;
data want;
set have;
by order set;

attrib
Page format=4.
;

if _n_ = 1 then do;
FromSet = Set;
declare hash tracks();
tracks.defineKey('Track');
tracks.defineData('Page');
tracks.defineData('FromSet');
tracks.defineDone();
end;

if first.order then
tracks.Clear();

if first.set then
seq_TrackInSet = 0;

if tracks.find() ne 0 then do; /* new track in set */
seq_TrackInSet + 1;
if seq_TrackInSet <= 10 then do;
Page = seq_TrackInSet;
FromSet = Set;
tracks.add();
end;
end;

* for edification, flag the wanted row;
if tracks.num_items <= 100 and 1 <= seq_TrackInSet <= 10 and Set=FromSet then want_row = '*';

if want_row = '*'; *subsetting if;

drop want_row seq_TrackInSet;

[RichardDeVen]

Ran your code and didn't get any errors.  What version of SAS are you running (%put &SYSVLONG4) ?  What client (PC sas, EG, Studio, other) are you using ? Can you post or attach the entire log after running the code in a fresh session ?

[PROCDATARUN]

On SAS EG 7.1

GOPTIONS ACCESSIBLE;
26 data have;
27 input
28 Order: $4. Set: $4. Track: $7. SubGroup: $1.;
29 datalines;

NOTE: LOST CARD.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
65 ;
Order=O346 Set=O346 Track=O346 J0 SubGroup= _ERROR_=1 _N_=9
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.HAVE has 8 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

65 ;

66 data want;
67 set have;
68 by order set;
69
70 attrib
71 Page format=4.
72 ;
73
74 if _n_ = 1 then do;
75 FromSet = Set;
76 declare hash tracks();
77 tracks.defineKey('Track');
78 tracks.defineData('Page');
79 tracks.defineData('FromSet');
2 The SAS System Monday, June 15, 2020 11:18:00 PM

80 tracks.defineDone();
81 end;
82
83 if first.order then
84 tracks.Clear();
85
86 if first.set then
87 seq_TrackInSet = 0;
88
89 if tracks.find() ne 0 then do; /* new track in set */
90 seq_TrackInSet + 1;
91 if seq_TrackInSet <= 10 then do;
92 Page = seq_TrackInSet;
93 FromSet = Set;
94 tracks.add();
95 end;
96 end;
97
98 * for edification, flag the wanted row;
99 if tracks.num_items <= 100 and 1 <= seq_TrackInSet <= 10 and Set=FromSet then want_row = '*';
100
101 if want_row = '*'; *subsetting if;
102
103 drop want_row seq_TrackInSet;
104
105 GOPTIONS NOACCESSIBLE;
106 %LET _CLIENTTASKLABEL=;
107 %LET _CLIENTPROCESSFLOWNAME=;
108 %LET _CLIENTPROJECTPATH=;
109 %LET _CLIENTPROJECTPATHHOST=;
110 %LET _CLIENTPROJECTNAME=;
111 %LET _SASPROGRAMFILE=;
112 %LET _SASPROGRAMFILEHOST=;
113
114 ;*';*";*/;quit;run;
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 6 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

115 ODS _ALL_ CLOSE;
116
117
118 QUIT; RUN;
119