transforming from long to wide

[femiajumobi1]

Hi I am trying to transpose the population data below from long (data A) to wide (data B)

Data A.

age      pop  counties

1           10     A

2          5        A

3          7       A

1           6       B

2          4       B

3          5      B

1           8     C

2          9      C

3          6      C

I want to to look like this (counties, numeric of age (1 2 3), sum total of age by county (total):

Data B. 

Counties  1        2         3      total

A               10      5        7       22

B               6        4        5         15

C              8         9        6         23

But the code below is not working. Thank you

proc transpose data=A out=B;
by counties;
id age;
var pop;
run;

[Tom · Accepted solution]

The code you showed works for your example data.  So please explain what it not working.

You should add the PREFIX= option so the variable names generated are actual variable names and not digit strings.

data have;
  input age pop counties $;
cards;
1 10 A
2 5 A
3 7 A
1 6 B
2 4 B
3 5 B
1 8 C
2 9 C
3 6 C
;

proc transpose data=have 
  out=want(drop=_name_) prefix=age 
;
  by counties;
  id age;
  var pop;
run;

proc print;
run;

Result

Obs    counties    age1    age2    age3

 1        A         10       5       7
 2        B          6       4       5
 3        C          8       9       6

[femiajumobi1]

On adding the prefix =AGE
it gives beyond what is required
age1 age2 age3 ...........age_n

My goal is to make the data wide with the counties as column, ages 1-3 as column and the content of the ages1-3 should be the population which I have to sum up in another column.

First step is to get the population for each age to reflect within the ages pivoted wide. Thanks

[Tom]

Please provide an example dataset that demonstrates the issue.

It will only give you variables (columns are something that you put into a spreadsheet or a report) for the values of AGE that exist in your dataset.  If you don't want to transpose the observations (rows are something that you put into a spreadsheet or a report) that have other values of AGE then use a WHERE statement to eliminate those observations.

If you want to make a new variable with the total for all of the new AGEnn variables in each observation then add another step.

data want;
  set want;
  total = sum(of age:);
run;

[femiajumobi1]

Thanks but How do I drop AGE from AGE1 AGE2 AGE3?

I want to have the columns as: 1 2 3 instead of AGE1 AGE2 AGE3

[yabwon]

you can't, see documentation: https://documentation.sas.com/doc/ru/pgmsascdc/v_046/lepg/n1m3fal4mygiy0n1fvq8v5ax2jfn.htm

Bart

[femiajumobi1]

Really? What of doing so to export as xls or csv file?

[Kurt_Bremser]

Exporting to text (csv) is easy, as in the DATA step you write the header line yourself:

data _null_;
set your_dataset;
file "path_to_your_file" dlm="," dsd;
if _n_ = 1 then put "counties,1,2,3,total";
put
  counties
  age1
  age2
  age3
  total
;
run;

For Excel, use ODS EXCEL, set 1,2 and 3 as labels for age1, age2 and age3, and use PROC PRINT with the LABEL option.

[Tom]

---

@femiajumobi1 wrote:
Thanks but How do I drop AGE from AGE1 AGE2 AGE3?

I want to have the columns as: 1 2 3 instead of AGE1 AGE2 AGE3

---

Don't use the PREFIX= option. But those are TERRIBLE names for variables.

If you want a REPORT then don't bother to make a dataset.

Just use PROC TABULATE or PROC REPORT to make your table (as in a table or figure included in your manuscript.) instead of making a dataset.