Need to create a flag if same Severity for same subject and cat and loc but with different visit

[ambadi007]

Hi Team,

I have a data like below

Subject category subcategory location visitnum severity wanted_flag

100 UAE Dubai Sharja 90 MILD N

100 UAE Dubai Sharja 20 MILD N

101 UAE Dubai Abudabi 90 MILD Y

101 UAE Dubai Abudabi 20 MODERATE Y

102 UAE Dubai Al Ain 20 MODERATE Y

102 UAE Dubai Kuwait 20 MODERATE Y

102 IND MUMBAI Delhi 20 MILD Y

103 IND Chennai Kolkata 90 MODERATE N

103 IND Chennai Kolkata 20 MODERATE N

The needed flag is also created above. Any help appreciated 

[PeterClemmensen · Accepted solution]

Try this

data have;
input Subject category $ subcategory $ location $ visitnum severity $;
datalines;
100 UAE Dubai Sharja  90 MILD     
100 UAE Dubai Sharja  20 MILD     
101 UAE Dubai Abudabi 90 MILD     
101 UAE Dubai Abudabi 20 MODERATE 
;

proc sql;
   create table want as
   select *
        , ifc(count(distinct severity) > 1, 'Y', 'N') as flag 
   from have
   group by Subject, category, subcategory
   ;
quit;

Result:

Subject category subcategory location visitnum severity flag
100     UAE      Dubai       Sharja   90       MILD     N
100     UAE      Dubai       Sharja   20       MILD     N
101     UAE      Dubai       Abudabi  90       MILD     Y
101     UAE      Dubai       Abudabi  20       MODERATE Y

[ambadi007]

Now Iam getting "N" for if the same Subject same category and Same subcategory same visits
and with different location . This Should be Y .. also if a subject is present with only one visits as well then also should get Y