Desktop productivity for business analysts and programmers

How do I create a new category based on multipe rows for one person?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

How do I create a new category based on multipe rows for one person?

 

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

Accepted Solutions
Solution
‎01-03-2017 12:53 PM
Respected Advisor
Posts: 4,821

Re: How do I create a new category based on multipe rows for one person?

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


All Replies
Occasional Contributor
Posts: 6

Re: How do I create a new category based on multipe rows for one person?

I am using SAS EG 4.2. Thanks!

Solution
‎01-03-2017 12:53 PM
Respected Advisor
Posts: 4,821

Re: How do I create a new category based on multipe rows for one person?

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
Occasional Contributor
Posts: 6

Re: How do I create a new category based on multipe rows for one person?

Thanks PGStats! I will try the query this afternoon. I really appreciate the quick response.
Frequent Contributor
Posts: 105

Re: How do I create a new category based on multipe rows for one person?

[ Edited ]

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;

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 238 views
  • 1 like
  • 3 in conversation