DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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

Hi

 

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


Accepted Solutions
Solution
‎06-14-2018 03:21 AM
Super User
Posts: 10,211

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎06-14-2018 03:21 AM
Super User
Posts: 10,211

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 15

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

Posted in reply to KurtBremser

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

Super Contributor
Posts: 339

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

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.

Occasional Contributor
Posts: 15

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

Thanks for your help
PROC Star
Posts: 1,770

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 104 views
  • 1 like
  • 4 in conversation