DATA Step, Macro, Functions and more

How to take multiple observations and reduce to a single observation by category?

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to take multiple observations and reduce to a single observation by category?

[ Edited ]

Hi SAS Community,

I have a dataset where there are multiple observations per category, and I need to reduce this dataset to a single observation per category.

Here is an example of my data...can someone help me code this up?

 

data have;

typebrand
car bmw
car lexus
car toyota
car honda
car maserati
car ford
truckford
truckchevy
trucktoyota
truckdodge
motorcycleharleydavidson
motorcyclehonda

 

data want;

typebrand
carbmw; lexus; toyota;honda; maserati; ford
truckford; chevy; toyota; dodge
motorcycleharleydavidson; honda

 

THanks


Accepted Solutions
Solution
‎07-19-2018 11:16 AM
PROC Star
Posts: 631

Re: How to take multiple observations and reduce to a single observation by category?

Here are two methods:

data have;
input type:$20.	brand:$20.;
datalines;
car bmw
car lexus
car toyota
car honda
car maserati
car ford
truck ford
truck chevy
truck toyota
truck dodge
motorcycle harleydavidson
motorcycle honda
;
run;
proc sort data=have;
by type;
run;
/* Method 1 */
data want(drop=brand_);
format brand $100.;
do until(last.type);
set have(rename=(brand=brand_));
by type;
brand=strip(brand)||";"||strip(brand_);
end;
run;
/* Method 2 */
proc transpose data=have out=trn(drop=_name_);
by type;
var brand;
run;
data want(drop=COL:);
set trn;
brand=CATX(";",of col:);
run;
Thanks,
Suryakiran

View solution in original post


All Replies
Super User
Posts: 10,850

Re: How to take multiple observations and reduce to a single observation by category?

data have;
infile cards expandtabs truncover;
input type	$ brand $;
cards;
car 	bmw
car 	lexus
car 	toyota
car 	honda
car 	maserati
car 	ford
truck	ford
truck	chevy
truck	toyota
truck	dodge
;
run;
data want;
length want $ 200;
 do until(last.type);
  set have;
  by type;
  want=catx(';',want,brand);
 end;
 drop brand;
 run;
Contributor
Posts: 45

Re: How to take multiple observations and reduce to a single observation by category?

@belboy
       Can you try something like this ? 

 

ods noproctitle;

proc freq data=SASHELP.CARS;
	tables  (Type) *(Make) / chisq nopercent norow nocol nocum 
		plots(only)=(freqplot mosaicplot);
run;
Solution
‎07-19-2018 11:16 AM
PROC Star
Posts: 631

Re: How to take multiple observations and reduce to a single observation by category?

Here are two methods:

data have;
input type:$20.	brand:$20.;
datalines;
car bmw
car lexus
car toyota
car honda
car maserati
car ford
truck ford
truck chevy
truck toyota
truck dodge
motorcycle harleydavidson
motorcycle honda
;
run;
proc sort data=have;
by type;
run;
/* Method 1 */
data want(drop=brand_);
format brand $100.;
do until(last.type);
set have(rename=(brand=brand_));
by type;
brand=strip(brand)||";"||strip(brand_);
end;
run;
/* Method 2 */
proc transpose data=have out=trn(drop=_name_);
by type;
var brand;
run;
data want(drop=COL:);
set trn;
brand=CATX(";",of col:);
run;
Thanks,
Suryakiran
Contributor
Posts: 40

Re: How to take multiple observations and reduce to a single observation by category?

Posted in reply to SuryaKiran

worked like a charm!  thanx

PROC Star
Posts: 549

Re: How to take multiple observations and reduce to a single observation by category?

one more way

data want;
length all_brand $100.;
retain all_brand;
set have;
by type;
if first.type then all_brand = brand;
else all_brand= catx(';', brand, all_brand);
if last.type then output;
drop brand;
run;
Super User
Posts: 13,942

Re: How to take multiple observations and reduce to a single observation by category?


@belboy wrote:

Hi SAS Community,

I have a dataset where there are multiple observations per category, and I need to reduce this dataset to a single observation per category.


What will you be doing with the resulting data? It may be harder to work with that form than you expect.

For example, in your expected  want output that ford and Toyota appear in different order and positions. Anything where you need the individual names later is going to be a pain to work with. Also you example is not quite consistent about separating the names with a semicolon or semicolon and space.


data want;

type brand
car bmw; lexus; toyota;honda; maserati; ford
truck ford; chevy; toyota; dodge
motorcycle harleydavidson; honda

 


 

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 90 views
  • 0 likes
  • 6 in conversation