IF first or last or another step

[Syndey]

Hi I have some data below and from the data I want:

1. A maximum of 6 records per customer

2. From the 6 records I want on only record from Pet and one from Home if in rank 1 to 6 and then the remainder to be Food. However, if Home and Pet are not in rank 1 to 6 for each customer group then just select Food even though rank 7 could be Home or Pet(See cust_ID C as what I mean by this) Also not all customers will have a max of 6 records, some might have five (see cust_id b as the example). I have highlighted in red which records I want selected using the above logic Thanks

Cust_ID	Rank	Group
A	1	FOOD
A	2	PET
A	3	PET
A	4	HOME
A	5	FOOD
A	6	FOOD
A	7	FOOD
A	8	FOOD
B	1	PET
B	2	FOOD
B	3	PET
B	4	FOOD
B	5	FOOD
C	1	FOOD
C	2	FOOD
C	3	FOOD
C	4	FOOD
C	5	FOOD
C	6	FOOD
C	7	PET
C	8	HOME

[sbb]

Suggest the task be broken down into smaller subtask objectives, and explore these SAS programming techniques to achieve your task's overall objective:

1) SAS SORT for ordering observations in advance of #2 below.

2) SAS DATA step with SET / BY processing to identify FIRST.<break_variable> and LAST.<break_variable> processing, where needed.

3) enhanced diagnostic SASLOG output by using PUTLOG _ALL_;  which helps by revealing the FIRST. and LAST. conditions based on the DATA step BY variable list.

4) possibly consider the DATA step LAG function for capturing prior observation SAS variable values; also RETAIN statement for retaining values across DATA step execution iterations.

Scott Barry

SBBWorks, Inc.

[art297]

If your data are already sorted in the order shown than the following (I think) will produce the result that you indicated:

data want;

  set have;

  by Cust_ID;

  if first.Cust_ID then do;

    pet=0;

    home=0;

    counter=0;

  end;

  if group eq 'PET' and pet eq 0 and rank le 6 then do;

    pet+1;

    counter+1;

    output;

  end;

  else if group eq 'HOME' and home eq 0 and rank le 6 then do;

    home+1;

    counter+1;

    output;

  end;

  else if group eq 'FOOD' and counter le 5 then do;

    counter+1;

    output;

  end;

run;

[Ksharp]

If I understood what you mean.

data have;
input id $ rank group $;
cards;
A     1     FOOD
A     2     PET
A     3     PET
A     4     HOME
A     5     FOOD
A     6     FOOD
A     7     FOOD
A     8     FOOD
B     1     PET
B     2     FOOD
B     3     PET
B     4     FOOD
B     5     FOOD
C     1     FOOD
C     2     FOOD
C     3     FOOD
C     4     FOOD
C     5     FOOD
C     6     FOOD
C     7     PET
C     8     HOME
;
data want;
 set have;
 by id;
 if first.id then call missing(pet,home,n);
 if group='PET' then pet+1;
  else if group='HOME' then home+1;
 if group='FOOD' or  pet=1 or  home=1 then do;n+1;if n lt 7 then output;end;
 drop n pet home;
run;

Xia Keshan