topic Re: sorting dataset descending frequency in SAS Procedures

sorting dataset descending frequency

michtka — Sun, 19 May 2013 19:13:53 GMT

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

Re: sorting dataset descending frequency

PGStats — Sun, 19 May 2013 22:16:27 GMT

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;

Re: sorting dataset descending frequency

michtka — Sun, 19 May 2013 22:40:24 GMT

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.

Re: sorting dataset descending frequency

PGStats — Mon, 20 May 2013 01:24:10 GMT

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

Re: sorting dataset descending frequency

michtka — Mon, 20 May 2013 01:32:05 GMT

sorry, I got confused , thanks.

Re: sorting dataset descending frequency

michtka — Mon, 20 May 2013 02:35:23 GMT

Whatg do you think about this one?

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;