BookmarkSubscribeRSS Feed
Calcite | Level 5

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";
proc print data=Med1;
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";
proc print data=Med2;

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!

Opal | Level 21

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?

Super User

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

Opal | Level 21

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";

not work?

Super User

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.

Community Manager

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 median;
 class origin;
 var msrp;
 output out=medians median=msrpMedian;

/* 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,
     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 t1 left join medians (where=(_type_=1)) t2 
  on t1.origin = t2.origin
  order by model;




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 5 replies
  • 6 in conversation