BookmarkSubscribeRSS Feed
Syndey
Calcite | Level 5

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
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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
Opal | Level 21

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 795 views
  • 1 like
  • 4 in conversation