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
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
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
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.
I don't understand your request...:smileyconfused: There is no hash code in the solution above. Did you mean SQL?
sorry, I got confused , thanks.
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;
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.