topic Re: how to work with unusual column names after transpose? in SAS Programming

how to work with unusual column names after transpose?

pensarchem — Wed, 21 Aug 2019 18:25:16 GMT

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.

Re: how to work with unusual column names after transpose?

PaigeMiller — Wed, 21 Aug 2019 18:34:43 GMT

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.

Re: how to work with unusual column names after transpose?

pensarchem — Wed, 21 Aug 2019 18:38:37 GMT

Hi Paige,

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

Re: how to work with unusual column names after transpose?

PaigeMiller — Wed, 21 Aug 2019 18:43:57 GMT

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.

Re: how to work with unusual column names after transpose?

Reeza — Wed, 21 Aug 2019 18:55:17 GMT

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.

Re: how to work with unusual column names after transpose?

pensarchem — Wed, 21 Aug 2019 19:50:40 GMT

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.

Re: how to work with unusual column names after transpose?

PaigeMiller — Wed, 21 Aug 2019 20:03:45 GMT

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

Re: how to work with unusual column names after transpose?

Reeza — Wed, 21 Aug 2019 20:09:49 GMT

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