Making Categorical Variables and Changing from Multiple Observations to One

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Making Categorical Variables and Changing from Multiple Observations to One

I'm sort of new to SAS so I apologize if what I'm about to ask is pretty straight forward.

I have a dataset that I'm using in SAS. Basically what it has in it right now is: LocationNumber, Gender, Population, and Agegroup.

Agegroup is categorical right now as the following: "1 to 2 years" "2 to 3 years" "4 to 5 years" "6 to 7 years" "8 to 9 years" "10 to 11 years" and so on. I've changed this "Agegroup variable" to another variable called Agegp - in this group what was "1 to 2 years" is now just "1" and so on. However I was hoping to combine the last 2 groups into one group - so originally they would have been "13 to 14 years" and "15 to 16 years" ( it would be pretty straightforward for me to call them Agegp="8" and Agegp="9" respectively) but I'd like them both to be Agegp="8" (essentially a anything above 13 years group). When I put in the statement:

if (Agegroup= "13 to 14 years" or Agegroup="15 to 16 years") then Agegp='8';

then SAS has 2 observations (keeping the rows as they would have been had they been labeled as before) for that location as below:

LocationNumber Gender Population Agegp

1                               1             5             8

1                               1             2             8

How do I go about combining those 2 observational rows together? I basically want it to look like the following:

LocationNumber Gender Population Agegp

1                              1                7             8

Any help/advice/tips/anything would be greatly appreciated.


Accepted Solutions
Solution
‎11-13-2014 04:19 PM
Trusted Advisor
Posts: 1,203

Re: Making Categorical Variables and Changing from Multiple Observations to One

proc sql;

create table want as

select LocationNumber, Gender, sum(Population) as Population, Agegp from have

group by LocationNumber, Gender,Agegp;

quit;

View solution in original post


All Replies
Grand Advisor
Posts: 17,396

Re: Making Categorical Variables and Changing from Multiple Observations to One

proc means data=havesum;

class locationNumber Gender AgeGP;

var population;

output out=want sum(population)=Population;

run;

Why are you doing this though? Quite a few procs have the Class statement that allow you to define a variable as categorical and SAS will automatically create the appropriate categorical variables for the analysis.

For recoding, I generally recommend PROC FORMAT, so it's something you may want to look into:

http://www2.sas.com/proceedings/sugi30/001-30.pdf

New Contributor
Posts: 4

Re: Making Categorical Variables and Changing from Multiple Observations to One

Thank-you Reeza for the information. I'm trying to create a dataset so that I can use this in the future for other analyses (merging it with other files, etc). So while your proc means to calculate the population works, I still need to be able to combine the rows in the SAS dataset so that it can be merged in the future. (I hope I'm making sense on this one). I have to recode the agegroups to be consistent with other file formats that are in existence for other datasets.

Grand Advisor
Posts: 17,396

Re: Making Categorical Variables and Changing from Multiple Observations to One

The proc means generates a WANT dataset that has the values summarized.

Solution
‎11-13-2014 04:19 PM
Trusted Advisor
Posts: 1,203

Re: Making Categorical Variables and Changing from Multiple Observations to One

proc sql;

create table want as

select LocationNumber, Gender, sum(Population) as Population, Agegp from have

group by LocationNumber, Gender,Agegp;

quit;

New Contributor
Posts: 4

Re: Making Categorical Variables and Changing from Multiple Observations to One

Thank-you! I thought it was an SQL statement I needed- I got a little crazy on the select line though. Much thanks as it works!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 218 views
  • 0 likes
  • 3 in conversation