transpose

[Amalik]

Hi,

I want to transpose my data such that it converts as follows: 

	Before
ID	1985	1986	1987
1	3	4	5
2	4	5	6
3	5	6	7

	After
1	1986	3
1	1987	4
1	1988	5
2	1986	4
2	1987	5
2	1988	6
3	1986	5
3	1987	6
3	1988	7

Suggestions, please.

Cheers,

AMalik

[Shmuel · Accepted solution]

What are your sas variable names? 1985, 1986 etc. are not valid names in sas.

You can use proc transpose to get the wanted result, probably with one more step to get the year out of the _NAME_;

Try it and post your code and results in case of any issue.

[Amalik]

Thanks, I was just not using valid SAS name

[Kurt_Bremser]

Post your REAL data (pure numbers are NOT valid SAS names for columns), in the form of a data step, so we can easily recreate the dataset for testing, as it is. All other methods do not convey important information about the dataset in question.

Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert a dataset to a data step, if you have trouble writing that data step on your own.

Post the resulting code either in a "little running man" or {i} window, as both keep formatting an content. Posting code or data text in the main posting window will invariably mangle the content.

[RW9]

If before looks like:

id _1985 _1986 _1987 - note the underscore to make them valid variable names then:

proc transpose data=have out=want;
  by id;
  var _:;
run;

Should work.  If ID is text then you can also do:

proc transpose data=have out=want;
  by id;
  var _numeric_;
run;