topic Re: transforming from long to wide in New SAS User

transforming from long to wide

femiajumobi1 — Mon, 05 Feb 2024 17:10:59 GMT

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;

Re: transforming from long to wide

Tom — Mon, 05 Feb 2024 18:03:31 GMT

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

Re: transforming from long to wide

femiajumobi1 — Mon, 05 Feb 2024 18:28:17 GMT

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

Re: transforming from long to wide

Tom — Mon, 05 Feb 2024 18:32:55 GMT

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;

Re: transforming from long to wide

femiajumobi1 — Mon, 05 Feb 2024 19:32:15 GMT

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

Re: transforming from long to wide

yabwon — Mon, 05 Feb 2024 19:43:06 GMT

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

Bart

Re: transforming from long to wide

femiajumobi1 — Mon, 05 Feb 2024 19:53:47 GMT

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

Re: transforming from long to wide

Tom — Mon, 05 Feb 2024 20:13:37 GMT

@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.

Re: transforming from long to wide

Kurt_Bremser — Mon, 05 Feb 2024 20:15:58 GMT

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.