## sorting dataset descending frequency

Solved
Super Contributor
Posts: 301

# sorting dataset descending frequency

Hi guys, I have got this dataset called have:

data have;

length nintensity ncolor 8.;

length value intensity color \$10.;

input value nintensity \$ intensity  \$ color ncolor;

datalines;

house 2 Mild      Blue   1

house 1 Moderate  Red    1

house 2 Severe    Green  2

house 2 Mild      Green  1

car   2 Mild      Black  1

car   3 Severe    Black  2

car   2 Moderate  Black  2

car   2 Mild      Yellow 1

car   3 Severe    Yellow 1

;

run;

And I would like to create a final dataset WANT sorting out in descending frequency in value and intensity , to finally get something like this, could you help me with this?  Thanks in advance.ency

CAR      Severe               6

CAR      Moderate           2

CAR      Mild                  4

Black     Severe             2

Black     Moderate         2

Black     Mild                1

Yellow    Severe            1

Yellow    Mild                1

HOUSE Severe              2

HOUSE Moderate          4

HOUSE Mild                 1

Green    Severe             2

Green    Mild                1

Red       Moderate         1

Blue      Mild                 1

Accepted Solutions
Solution
‎05-19-2013 06:16 PM
Posts: 5,526

## Re: sorting dataset descending frequency

Looks simple, but it isn't (at least not to me!) :

data have;
length nintensity ncolor 8.;
length value intensity color \$10.;
input value nintensity \$ intensity  \$ color ncolor;
datalines;
house 2 Mild      Blue   1
house 1 Moderate  Red    1
house 2 Severe    Green  2
house 2 Mild      Green  1
car   2 Mild      Black  1
car   3 Severe    Black  2
car   2 Moderate  Black  2
car   2 Mild      Yellow 1
car   3 Severe    Yellow 1
;

data haveExo(keep=cat level intensity order n color);
set have;
cat = value;
level=value; order = 0; n = nintensity; output;
level=color; order = 1; n = ncolor    ; output;
run;

proc sql;

create table want as
select
h.level,

h.intensity,
sum(n) as n
from
haveExo  as h natural join
(select cat, level, count(*) as freqLev from haveExo group by cat, level) natural join
(select cat, count(*) as freqCat from haveExo where cat=level group by cat)
group by freqCat, h.cat, h.order, freqLev, h.level, h.intensity
order by freqCat desc, h.cat, h.order, freqLev desc, h.intensity desc;
quit;

PG

PG

All Replies
Solution
‎05-19-2013 06:16 PM
Posts: 5,526

## Re: sorting dataset descending frequency

Looks simple, but it isn't (at least not to me!) :

data have;
length nintensity ncolor 8.;
length value intensity color \$10.;
input value nintensity \$ intensity  \$ color ncolor;
datalines;
house 2 Mild      Blue   1
house 1 Moderate  Red    1
house 2 Severe    Green  2
house 2 Mild      Green  1
car   2 Mild      Black  1
car   3 Severe    Black  2
car   2 Moderate  Black  2
car   2 Mild      Yellow 1
car   3 Severe    Yellow 1
;

data haveExo(keep=cat level intensity order n color);
set have;
cat = value;
level=value; order = 0; n = nintensity; output;
level=color; order = 1; n = ncolor    ; output;
run;

proc sql;

create table want as
select
h.level,

h.intensity,
sum(n) as n
from
haveExo  as h natural join
(select cat, level, count(*) as freqLev from haveExo group by cat, level) natural join
(select cat, count(*) as freqCat from haveExo where cat=level group by cat)
group by freqCat, h.cat, h.order, freqLev, h.level, h.intensity
order by freqCat desc, h.cat, h.order, freqLev desc, h.intensity desc;
quit;

PG

PG
Super Contributor
Posts: 301

## Re: sorting dataset descending frequency

Thanks PG Stat for your contribution,

Please, could you supply your hash code for other code, I am not familiar with the hash functions,

Thank you.

Posts: 5,526

## Re: sorting dataset descending frequency

I don't understand your request...:smileyconfused: There is no hash code in the solution above. Did you mean SQL?

PG
Super Contributor
Posts: 301

## Re: sorting dataset descending frequency

sorry, I got confused , thanks.

Super Contributor
Posts: 301

## Re: sorting dataset descending frequency

I am wondering if I can simplify step (merge proc sql) and (proc sort) in only only an  proc sql...Thanks in advance.

***sum of severities by value and intensity;

proc sql noprint;

create table want2 as

select upcase(value) as value, intensity,nintensity,color, ncolor, sum (nintensity) as n1

from have

group by value,intensity

order by value, color, intensity descending;

quit;

**flaging priority (Severe>Moderate>Mild);

data havex;

set have;

if upcase(intensity)="SEVERE" then flag=3;

else if upcase(intensity)="MODERATE" then flag=2;

else if upcase(intensity)="MILD" then flag=1;

run;

***first proc sql to create the order of value in the variable label;

proc sql noprint;

create table want2x as

select value, intensity,nintensity,color, ncolor, sum(flag) as col1

from havex

group by  value, color

order by value, color, intensity descending;

quit;

***second proc sql to create the order of color in the variable label

proc sql noprint;

create table want3x as

select value, intensity,nintensity,color, ncolor, sum(flag) as col0

from havex

group by value

order by value, color, intensity descending;

quit;

data want3;

merge want2x want3x;

by value color;

run;

proc sort data=want3 out=want3s;by descending col0 descending col1 color descending intensity;run;

**create the variable label;

data want4 (keep=label value color count count2);

set want3s;

by descending col0 descending col1;

if first.col0 then do;

label=upcase(value);

count+1;

count2=0;

output;

end;

if first.col1 then do;

label=color;

count+1;

count2=1;

output;

end;

run;

**merge proc sql;

proc sql noprint;

create table want5 as

select  a.label,a.count,a.count2,b.color,b.ncolor,b.intensity,n1

from want4 as a left join want2 as b

on a.label=b.value or a.color=b.color

order by count,color, intensity descending;

quit;

***proc sort; (I am wondering if I can include this step in the above step);  Need help for this one

proc sort data=want5 out=want5s nodupkey;

by count descending intensity;

run;

Final dataset wantx;

data wantx (keep=label intensity ncolor);

set want5s;

by count;

if count2=0 then ncolor=n1;

run;

🔒 This topic is solved and locked.