topic IF first or last or another step in SAS Programming

IF first or last or another step

Syndey — Wed, 11 Mar 2015 21:47:27 GMT

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

Re: IF first or last or another step

sbb — Wed, 11 Mar 2015 22:05:38 GMT

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.

Re: IF first or last or another step

art297 — Wed, 11 Mar 2015 22:24:31 GMT

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;

Re: IF first or last or another step

Ksharp — Thu, 12 Mar 2015 09:53:48 GMT

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