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

Solved
Occasional Contributor
Posts: 6

# 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_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

Accepted Solutions
Solution
‎01-03-2017 12:53 PM
Posts: 5,624

## 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

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
Posts: 5,624

## 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: 121

## 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.