BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SimonJ
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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.

SimonJ
Fluorite | Level 6

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

Amir
PROC Star

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.

novinosrin
Tourmaline | Level 20

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;

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
  • 5 replies
  • 846 views
  • 1 like
  • 4 in conversation