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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.