BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michtka
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

5 REPLIES 5
PGStats
Opal | Level 21

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
michtka
Fluorite | Level 6

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.

PGStats
Opal | Level 21

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

PG
michtka
Fluorite | Level 6

sorry, I got confused , thanks.

michtka
Fluorite | Level 6

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;

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1010 views
  • 3 likes
  • 2 in conversation