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

 

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_IDDAYSACTIVITY
1234148WALK
12347RUN
9999186RUN
999930WALK
222295RUN
222295WALK

 

Desired Output:

 

CUST_IDDAYSACTIVITYCAT
1234148WALKWALKER
12347RUNWALKER
9999186RUNRUNNER
999930WALKRUNNER
222295RUNWALK_RUN
222295WALKWALK_RUN
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

4 REPLIES 4
sas81
Fluorite | Level 6

I am using SAS EG 4.2. Thanks!

PGStats
Opal | Level 21

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;
PG
sas81
Fluorite | Level 6
Thanks PGStats! I will try the query this afternoon. I really appreciate the quick response.
devsas
Pyrite | Level 9

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;

 

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!

SAS Enterprise Guide vs. SAS Studio

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.

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