- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I want to know for every vehicle how much a problem occurs. So this means a problem can occurs more than once for every vehicle.
INPUT
VehicleType - Problem
AA-Z - Motor
AA-Z - Motor
AA-Z - Pedal
BB-C - Screen
BB-C - Screen
DD-F - Motor
GG-H - Motor
GG-H- Motor
GG-H - Motor
Expected Output
VehicleType - Problem - CountProblem
AA-Z - Motor - 2
BB-C -Screen - 2
DD-F - Motor - 1
GG-H - Motor- 3
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create table want as
select *
from (
select vehicletype, problem, count(*) as countproblem
from have
group by vehicletype, problem
)
group by vehicletype
having countproblem = max(countproblem)
;
quit;
Untested, for lack of usable example data (working data step with datalines).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create table want as
select *
from (
select vehicletype, problem, count(*) as countproblem
from have
group by vehicletype, problem
)
group by vehicletype
having countproblem = max(countproblem)
;
quit;
Untested, for lack of usable example data (working data step with datalines).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
That's not a valid SAS name, so the question is moot. Even with the option of validvarname=any, YOU DO NOT USE SUCH NAMES.
Fix such issues when importing data into SAS, change the hyphens to underlines.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Are VehicleType and Problem two separate variables?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have; input Vehicle $ Problem $; cards; AA-Z Motor AA-Z Motor AA-Z Pedal BB-C Screen BB-C Screen DD-F Motor GG-H Motor GG-H Motor GG-H Motor ; proc sql; create table temp as select vehicle,problem,count(*) as n from have group by vehicle,problem order by vehicle,n; quit; data want; set temp; by vehicle; if last.vehicle; run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
infile datalines dlmstr=' - ';
input VehicleType $ Problem $;
datalines;
AA-Z - Motor
AA-Z - Motor
AA-Z - Pedal
BB-C - Screen
BB-C - Screen
DD-F - Motor
GG-H - Motor
GG-H - Motor
GG-H - Motor
;
proc freq data=have noprint order=freq;
tables VehicleType*Problem / out=temp(drop=percent);
run;
data want;
set temp;
by VehicleType notsorted;
if first.VehicleType;
run;