transpose by two columns without set column length

[lmg]

I am trying to transpose a table without having a set column number.

My data looks like this but the columns across can be more or less (group and category columns always stay the same).

Group	Category	201931	201932	201933	201934	201935	201936	201937
A	wk	28	27	26	26	25	28	31
M	wk	28	27	26	26	25	28	31
Other	wk	28	27	26	26	25	28	31
A	bw	24	24	24	24	24	24	24
M	bw	24	24	24	24	24	24	24

This is what i need (not fully complete, but general idea): 

I need the column name to be in the row and the value associate with it.

Group	Category	week	value
A	wk	201931	28
A	wk	201932	27
A	wk	201933	26
A	wk	201934	26
A	wk	201935	25
A	wk	201936	28
A	wk	201937	31
M	wk	201931	28
M	wk	201932	27
M	wk	201933	26
M	wk	201934	26
M	wk	201935	25
M	wk	201936	28
M	wk	201937	31
M	wk	201931	28
M	wk	201932	27
M	wk	201933	26
M	wk	201934	26
M	wk	201935	25
M	wk	201936	28
M	wk	201937	31

I can use the proc contents to get the number of columns, but cannot transpose it properly.

Any items would be appreciated. Thank you.

[ballardw]

---

@lmg wrote:

I am trying to transpose a table without having a set column number.

My data looks like this but the columns across can be more or less (group and category columns always stay the same).

Group	Category	201931	201932	201933	201934	201935	201936	201937
A	wk	28	27	26	26	25	28	31
M	wk	28	27	26	26	25	28	31
Other	wk	28	27	26	26	25	28	31
A	bw	24	24	24	24	24	24	24
M	bw	24	24	24	24	24	24	24

This is what i need (not fully complete, but general idea): 

I need the column name to be in the row and the value associate with it.

Group	Category	week	value
A	wk	201931	28
A	wk	201932	27
A	wk	201933	26
A	wk	201934	26
A	wk	201935	25
A	wk	201936	28
A	wk	201937	31
M	wk	201931	28
M	wk	201932	27
M	wk	201933	26
M	wk	201934	26
M	wk	201935	25
M	wk	201936	28
M	wk	201937	31
M	wk	201931	28
M	wk	201932	27
M	wk	201933	26
M	wk	201934	26
M	wk	201935	25
M	wk	201936	28
M	wk	201937	31

I can use the proc contents to get the number of columns, but cannot transpose it properly.

Any items would be appreciated. Thank you.

---

Normally the restriction on variable names means that you can't have a variable name start with a digit. So, what are the actual names of your variables?

But all of those other variables all take numeric values then

proc transpose data=have out=trans

        name=week prefix=value

;

    by group category;

    var  _numeric_;

run;

should work assuming your data is sorted by Group and Category.

If your "week" variables have a mix of numeric and character values for some reason then you will need to create a data set that has all of one type or the other. You can't have mixed data types in a single output column as shown.