Subtracting one numeric value from another

[jcochrane424]

Hi everyone, 

So I'm having a little trouble in SAS 9.4. I'm working with projected population data from 2015 to 2050.

The data I'm working with looks as follows:

data have;
infile datalines dlm=',' dsd;
input date :$8. date :$10. gender :$20. age_group :25. population_in_thousands;
format population_in_thousands comma10.;
datalines;2015,both sexes,65 years and over,47695
2015,both sexes,85 years and over,6306
2015,female,65 years and over,26654
2015,female,85 years and over,4143
2015,male,65 years and over,21041
2015,male,85 years and over,2163;

 Here's an the code I have so far:

data New_projected_pop;
do until (last.date and last.gender);
set work.proj_pop_removebadages;
by date gender;
select (Age_group);
when ("85 years and over") over85 = population_in_thousands;
otherwise;
end;
end;
do until (last.date and last.gender);
set work.proj_pop_removebadages;
by date gender;
if Age_group = "65 years and over" and nmiss(over85) = 0
then population_in_thousands = population_in_thousands - over85;
output;
end;
run;

When I'm looking at the results of the code, it groups by date and only by the male gender, so the over85 column for each year of the projected pop. data is only subtracting the male value of the "85 years and over'" from the number for the "65 years and over" records. So in the example data it would be subtracting 2163 from the "65 years and over" values for all genders, not just male. I also know that the over85 column must be dropped, but have just been keeping it to check the results. If anyone knows how to group by gender as well so that it doesn't only look at the male value that would be great! Hopefully I've provided enough detail.

[hashman]

@jcochrane424:

Did you look at your input data after you created the data set? Your INPUT statement is messed up: It inputs DATE twice, and AGE_GROUP is read a a numeric variable while it's chock full of alphas. A correct input step, given your data, could look like this:

data have ;                                                                                                                             
  infile cards dlm=',' ;                                                                                                                
  input date :8. gender :$20. age_group :$25. population_in_thousands ;                                                                 
  format population_in_thousands comma10. ;                                                                                             
  cards ;                                                                                                                               
2015,both sexes,65 years and over,47695                                                                                                 
2015,both sexes,85 years and over,6306                                                                                                  
2015,female,    65 years and over,26654                                                                                                 
2015,female,    85 years and over,4143                                                                                                  
2015,male,      65 years and over,21041                                                                                                 
2015,male,      85 years and over,2163                                                                                                  
;                                                                                                                                       
run ;       

You can't expect correct output from incorrect input: It's otherwise known as GIGO. Now rerun your code against the correct input, and if you should have any incongruences you don't anticipate, ask about them.

Kind regards

Paul D.