Hello! Happy New Year!
Essentially I am looking for any help or guidance to develop a query that looks at both rows for each customer (i.e. cust_id) and places them in one of three categories based on their current activity or days.
Three categories: #1 Walker: Greater than 90 days of walking and less than 90 days of running
#2 Runner: Greater than 90 days of running and less than 90 days of walking
#3 Walker and Runner: Greater than 90 days of running and greater than 90 days of walking.
CUST_ID | DAYS | ACTIVITY |
1234 | 148 | WALK |
1234 | 7 | RUN |
9999 | 186 | RUN |
9999 | 30 | WALK |
2222 | 95 | RUN |
2222 | 95 | WALK |
Desired Output:
CUST_ID | DAYS | ACTIVITY | CAT |
1234 | 148 | WALK | WALKER |
1234 | 7 | RUN | WALKER |
9999 | 186 | RUN | RUNNER |
9999 | 30 | WALK | RUNNER |
2222 | 95 | RUN | WALK_RUN |
2222 | 95 | WALK | WALK_RUN |
A double do until loop can take care of this:
data have;
input CUST_ID DAYS ACTIVITY$;
datalines;
1234 148 WALK
1234 7 RUN
9999 186 RUN
9999 30 WALK
2222 95 RUN
2222 95 WALK
;
data want;
do until(last.cust_id);
set have; by cust_id notsorted;
if activity = "WALK" then walk=days;
else run = days;
end;
length cat $8;
if walk > 90 and run <= 90 then cat = "WALKER";
else if walk <= 90 and run > 90 then cat = "RUNNER";
else if walk > 90 and run > 90 then cat = "WALK_RUN";
else cat = "UNKNOWN";
do until(last.cust_id);
set have; by cust_id notsorted;
output;
end;
keep cust_id days activity cat;
run;
I am using SAS EG 4.2. Thanks!
A double do until loop can take care of this:
data have;
input CUST_ID DAYS ACTIVITY$;
datalines;
1234 148 WALK
1234 7 RUN
9999 186 RUN
9999 30 WALK
2222 95 RUN
2222 95 WALK
;
data want;
do until(last.cust_id);
set have; by cust_id notsorted;
if activity = "WALK" then walk=days;
else run = days;
end;
length cat $8;
if walk > 90 and run <= 90 then cat = "WALKER";
else if walk <= 90 and run > 90 then cat = "RUNNER";
else if walk > 90 and run > 90 then cat = "WALK_RUN";
else cat = "UNKNOWN";
do until(last.cust_id);
set have; by cust_id notsorted;
output;
end;
keep cust_id days activity cat;
run;
Another way I thought this could be solved is-
proc sort data =have;
by cust_id activity;
run;
proc transpose data = have out = have1 (drop = _name_);
by cust_id;
var days;
id activity;
run;
Data want;
set have1;
if walk>= 90 and run >= 90 then cat = "walk_run";
if walk <90 and run >= 90 then cat = "runner" ;
if walk >=90 and run <=90 then cat = "walker";
run;
proc transpose data = want out = want1;
by cust_id;
var run walk;
run;
proc sql;
create table want2 as select a.*, b.cat from want1 a inner join want b on a.cust_id = b.cust_id;
run;
data want3 (rename =( _name_= activity col1 = days)) ;
set want2;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.