BookmarkSubscribeRSS Feed
BankySm
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";
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!

5 REPLIES 5
Astounding
PROC Star

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
Super User

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

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

not work?

PG
Kurt_Bremser
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.

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

classvar.png

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2174 views
  • 2 likes
  • 6 in conversation