Assigning Page Numbers Based on Occurrences Regrouping

[PROCDATARUN]

Greetings SAS Experts,

Could you throw some help out here where the need is to assign page numbers based on occurrences?

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

The goal is to assign page number for the first 10 unique Tracknumbers and then push out the remaining Tracknumbers to the next page  2 but assigning them to the next available set number and so on until all tracknumbers are assigned to page

numbers for a given Ordernumber.

Example dataset below shows 12 unique track numbers for O123 Order, first 10 would go into its Page 1 assigned to SetN umber J001 and the remaining 2 on Page 2 but assigned to SetNumber J002.  Duplicate TrackNumbers assigned to SetNumber J002 should drop off as they were accounted for J001 Page number 1 for the given same order number O123.

Thanks a lot in advance for your help.

Input							DesiredOutput
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	1
O123	J001	3284G	B		O123	J001	3284G	B	1
O123	J001	5657G	A		O123	J001	5657G	A	1
O123	J001	490456B	B		O123	J001	490456B	B	1
O123	J001	490456C	C		O123	J001	490456C	C	1
O123	J001	490456D	A		O123	J001	490456D	A	1
O123	J001	490E	B		O123	J001	490E	B	1
O123	J001	490F	A		O123	J001	490F	A	1
O123	J001	490G	B		O123	J001	490G	B	1
O123	J001	490H	C		O123	J001	490H	C	1
O123	J001	490I	A		O123	J002	490I	A	2
O123	J001	490J	B		O123	J002	490J	B	2
O123	J002	456Y	A		O346	J001	456Y	A	1
O123	J002	456Y	B		O346	J001	456Y	B	1
O123	J002	456Y	C		O346	J001	456Y	C	1
O123	J002	3284G	A		O346	J001	3284G	A	1
O123	J002	3284G	B		O346	J001	3284G	B	1
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

[smantha]

OrderNumber

SetNumber

TrackNumber

SubGNumber

proc sort data=input_data;

by OrderNumber SetNumber TrackNumber;

data output_data;

set input_data;

by OrderNumber SetNumber TrackNumber;

retain counter ;

if first.OrderNumber then counter=10;

if last.OrderNumber then counter=counter-1;

PageNumber=2;

if counter  > 0 then PageNumber=1;

run;

[PROCDATARUN]

Hi Smantha,

It did not seem to work, not sure how would the code in there would reset counter for tracknumbers.

[smantha · Accepted solution]

My bad the last. should be last.track_number

proc sort data=input_data;

by OrderNumber SetNumber TrackNumber;

data output_data;

set input_data;

by OrderNumber SetNumber TrackNumber;

retain counter ;

if first.OrderNumber then counter=10;

if last.TrackNumber then counter=counter-1;

PageNumber=2;

if counter  > 0 then PageNumber=1;

run;