DATA Step, Macro, Functions and more

IF first or last or another step

Reply
Occasional Contributor
Posts: 5

IF first or last or another step

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_IDRankGroup
A1FOOD
A2PET
A3PET
A4HOME
A5FOOD
A6FOOD
A7FOOD
A8FOOD
B1PET
B2FOOD
B3PET
B4FOOD
B5FOOD
C1FOOD
C2FOOD
C3FOOD
C4FOOD
C5FOOD
C6FOOD
C7PET
C8HOME
Super Contributor
Super Contributor
Posts: 3,174

Re: IF first or last or another step

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.

PROC Star
Posts: 7,363

Re: IF first or last or another step

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;

Super User
Posts: 9,681

Re: IF first or last or another step

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

Ask a Question
Discussion stats
  • 3 replies
  • 229 views
  • 1 like
  • 4 in conversation