topic Re: How do I create a new category based on multipe rows for one person? in SAS Enterprise Guide

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

sas81 — Tue, 03 Jan 2017 16:14:34 GMT

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

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

sas81 — Tue, 03 Jan 2017 16:15:42 GMT

I am using SAS EG 4.2. Thanks!

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

PGStats — Tue, 03 Jan 2017 16:42:08 GMT

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;

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

sas81 — Tue, 03 Jan 2017 17:27:43 GMT

Thanks PGStats! I will try the query this afternoon. I really appreciate the quick response.

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

devsas — Thu, 05 Jan 2017 16:50:01 GMT

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;