topic Re: Customer in more than 1 group take the first group to create a new group. in SAS Programming

Customer in more than 1 group take the first group to create a new group.

SimonJ — Wed, 13 Jun 2018 11:32:31 GMT

I have following data

Customer group money

1 Group1 100

1 Group2 200

2 Group2 100

2 Group3 250

2 Group4 275

I need to create two new variables

the first one looking at group variable taking the first 1 group once sorted applied to records by Customer number

Customer group money new variable 1

1 Group1 100 Group1

1 Group2 200 Group1

2 Group2 100 Group2

2 Group3 250 Group2

2 Group4 275 Group2

The second one is again to look at the groups but take the group based on the last money field once sorted

Customer group money new variable 1 New Varible2

1 Group1 100 Group1 Group 2

1 Group2 200 Group1 Group 2

2 Group2 100 Group2 Group4

2 Group3 250 Group2 Group4

2 Group4 275 Group2 Group4

I have tried using Proc Sort then using first function but I am not sure how it works?

Any help would be greatly received.

Thanks

Simon

Re: Customer in more than 1 group take the first group to create a new group.

Kurt_Bremser — Wed, 13 Jun 2018 12:07:00 GMT

Try this in SQL;

proc sql;
create table want as
select
  a.customer, a.group, a.money,
  min(a.group) as new_var_1,
  (select group from have b where b.customer = a.customer group by b.customer having money = max(money)) as new_var_2
from have a
group by customer;
quit;

For a data step/sort solution, you would need two sorts:

proc sort data=have;
by customer group;
run;

data int1 (keep=customer new_var_1);
set have;
by customer;
if first.customer;
new_var_1 = group;
run;

proc sort data=have;
by customer money;
run;

data int2 (keep=customer new_var_2);
set have;
by customer;
if last.customer;
new_var_2 = group;
run;

data want;
merge
  have
  int1
  int2
;
by customer;
run;

Testing will reveal which of the solutions is better, performancewise, for larger datasets.

Re: Customer in more than 1 group take the first group to create a new group.

Amir — Wed, 13 Jun 2018 12:14:09 GMT

Hi,

If the data is sorted in the order presented then you could try:

data have_last;
   set have;
   by customer;

   if last.customer;
run;

data want;
   merge have
         have_last (rename = group = new_var_2);

   by customer;

   retain new_var_1;

   if first.customer then
      new_var_1 = group;
run;

Regards,

Amir.

Re: Customer in more than 1 group take the first group to create a new group.

novinosrin — Wed, 13 Jun 2018 14:33:17 GMT

Good morning and Hi @SimonJ If your dataset is sorted, i see the solution being very straight forward

data have;
input Customer     group  $                money;
cards;
1                  Group1                100
1                  Group2                200
2                  Group2                100 
2                  Group3                250
2                  Group4                275 
;


data want;
set have(in=a) have(in=b);
by customer  ;
retain new_var1 new_var2;
if a and first.customer then new_var1=group;
else if a then new_var2=group;
if b;
run;

Re: Customer in more than 1 group take the first group to create a new group.

SimonJ — Thu, 14 Jun 2018 07:21:22 GMT

Thanks I found this one did the job thanks for your help

Re: Customer in more than 1 group take the first group to create a new group.

SimonJ — Thu, 14 Jun 2018 07:22:41 GMT

Thanks for your help