How to create character variable with three levels based on condition, in SAS 9.4

[BankySm]

Hi all,

I'm a SAS beginner and have been struggling with the following problem for a few days now. I have a table with 4 variables: State, County, Poverty Density (PovDensity), Median Poverty Density(Median_Poverty_Density) and what I want to do is create a new character variable PovStatus with levels "Above State Median", "Below State Median"and "State Median" depending on whether each county's poverty density is above, below or equal to the state median poverty density. Now, my set contains three States: Alabama, Arkansas and Arizona but each of them has a bunch of different counties. Here is my code so far for Alabama:

Data Med1;
Set MergedTables;
where state="Alabama";
run;
proc print data=Med1;
run;
data Med2;
set Med1;
if PovDensity>10.287 then 
PovStatus="Above State Median";
else if PovDensity<10.287 then PovStatus="Below State Median";
else if PovDensity=10.287 then PovStatus="State Median";
run;
proc print data=Med2;
run;

It works but I don't think I'm supposed to be doing it like this for each state separately, instead I want something that checks these conditions at once for all three states and gives me the correct results. I would greatly appreciate any feedback/suggestions for how I might achieve that.

Thank you!

[Astounding]

Could PovDensity change from county to county, within the same State?  (If so, your current logic is incomplete.)

Does the data set MergedTables contain both County and State?

[Reeza]

Show the data you have please. Or make fake data that looks close enough. 

[PGStats]

Why would

data Med;
set MergedTables;
if PovDensity>Median_Poverty_Density then 
PovStatus="Above State Median";
else if PovDensity<Median_Poverty_Density then PovStatus="Below State Median";
else if PovDensity=Median_Poverty_Density then PovStatus="State Median";
run;

not work?

[Kurt_Bremser]

You don't need to tell us long stories about your tables, just post them in a data step with datalines. Then we will know everything (Maxim 3) about your existing data (variable names, types, formats, lengths, and content).

Post what you want to get out of it, so we can then bridge the gap in between with code.

[ChrisHemedinger]

Here's an example of what you're trying to do, I think, but using the sample CARS data set.  First you need the Median for each class value (State, in your case).  Then you need to merge & calculate a new variable based on this categorized value.

/* Calculate median at the category level */
proc summary data=sashelp.cars median;
 class origin;
 var msrp;
 output out=medians median=msrpMedian;
run;

/* Join the detail with the categorized median  */
/* And compute new conditional value using CASE */
proc sql;
 create table Result as 
  select t1.make, t1.model, t1.origin, t1.msrp, t2.msrpMedian,
    case
     when t1.msrp > t2.msrpMedian then "Above Origin Median"
     when t1.msrp = t2.msrpMedian then "At Origin Median"
     when t1.msrp < t2.msrpMedian then "Below Origin Median"
     else "Unknown"
    end as MsrpStatus
  from sashelp.cars t1 left join medians (where=(_type_=1)) t2 
  on t1.origin = t2.origin
  order by model;
quit;