transpose by two columns without set column length

lmg — Tue, 20 Aug 2019 19:45:19 GMT

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.

Re: transpose by two columns without set column length

ballardw — Tue, 20 Aug 2019 19:56:27 GMT

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

topic Re: transpose by two columns without set column length in New SAS User

transpose by two columns without set column length

Re: transpose by two columns without set column length