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

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

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

6 REPLIES 6
Ksharp
Super User
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;
UdayGuntupalli
Quartz | Level 8

@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;
SuryaKiran
Meteorite | Level 14

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
kiranv_
Rhodochrosite | Level 12

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;
ballardw
Super User

@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

 


 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 856 views
  • 0 likes
  • 6 in conversation