how to work with unusual column names after transpose?

[pensarchem]

CALYEAR prac_count freq_range
2011 1181 1--25
2011 616 26--50
2011 411 51--75
2011 591 76--99
2011 64 =100
2011 240 >100
2012 1125 1--25
2012 606 26--50
2012 461 51--75
2012 555 76--99
2012 451 =100
2012 8 >100

After transpose, the column headers become like this.

CALYEAR 1--25 26--50 51--75 76--99 =100 >100
2011 1181 616 411 591 64 240
2012 1125 606 461 555 451 8

Let's put >100 as example, it's char before transpose,  after type in sas, >100 becomes number.  Regardless, the format does not meet sas requirement anyway.

Let's say I want to sum along the row, how should I do? thanks.

[PaigeMiller]

After transpose, the column headers become like this.

CALYEAR 1--25 26--50 51--75 76--99 =100 >100

2011 1181 616 411 591 64 240
2012 1125 606 461 555 451 8

Actually, no they don't. Column names in SAS can't have a hyphen, equal sign or greater than (and a whole lot of other symbols also can't be in column names).

You could actually run PROC TRANSPOSE on this data and see what the column names become.

Let's say I want to sum along the row, how should I do? thanks.

Simple: don't do the transpose, sum the column of pract_count, BY CALYEAR.

[pensarchem]

Hi Paige,

The column headers show as I copied after the transpose..If that's the case, I will think other method then, thanks.

[PaigeMiller]

The column headers show as I copied after the transpose.

Those might be the column LABELS, but not the column NAMES. I guess it depends where you are looking to see these column names. (Where do you see this?)

If that's the case, I will think other method then, thanks.

The simpler method is to not trasnspose and then sum down the columns.

[Reeza · Accepted solution]

Run a proc contents to see the data set variable names and labels. 

proc contents data=sashelp.class; run;

For variable names with spaces and symbols you reference them using quotes around the name and an N after the quotation marks to indicate that it's a variable or dataset name. 

keep 'GROUP 01'n 'GROUP 02'n;

You can transpose and sum or you could sum directly in the original format if that's what you need. That's trivial using proc means. 

I illustrate some options to calculate a mean and add it to a data set or to just summarize data here:

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

I would probably recommend creating a variable that you could use instead when transposing so you have cleaner variable names but that's your choice. 

---

@pensarchem wrote:

CALYEAR prac_count freq_range
2011 1181 1--25
2011 616 26--50
2011 411 51--75
2011 591 76--99
2011 64 =100
2011 240 >100
2012 1125 1--25
2012 606 26--50
2012 461 51--75
2012 555 76--99
2012 451 =100
2012 8 >100

After transpose, the column headers become like this.

CALYEAR 1--25 26--50 51--75 76--99 =100 >100
2011 1181 616 411 591 64 240
2012 1125 606 461 555 451 8

Let's put >100 as example, it's char before transpose,  after type in sas, >100 becomes number.  Regardless, the format does not meet sas requirement anyway.

Let's say I want to sum along the row, how should I do? thanks.

---

[pensarchem]

thanks you very much, Reeza, it works.

Total = sum(of '1--25'n --'>100'n);

it's certainly not ideal to use these kinds of headers, but they are informative..It's good to keep them in the headers, thanks.

[PaigeMiller]

---

@pensarchem wrote:

thanks you very much, Reeza, it works.

Total = sum(of '1--25'n --'>100'n);

it's certainly not ideal to use these kinds of headers, but they are informative..It's good to keep them in the headers, thanks.

---

Then don't use these kinds of headers. Sum the columns instead of summing the rows.

[Reeza]

---

@pensarchem wrote:

thanks you very much, Reeza, it works.

Total = sum(of '1--25'n --'>100'n);

it's certainly not ideal to use these kinds of headers, but they are informative..It's good to keep them in the headers, thanks.

---

I disagree and would recommend keeping those as labels, not as variable names. The limits on variable names mean you actually lose information, whereas a label has a lot more flexibility. PROC TRANSPOSE also supports an IDLABEL statement to create nice clean labels. So I would not recommend using those as variable names either.