topic Re: transpose in SAS Programming

transpose

Amalik — Thu, 19 Oct 2017 05:33:35 GMT

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

Re: transpose

Shmuel — Thu, 19 Oct 2017 05:41:53 GMT

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.

Re: transpose

Kurt_Bremser — Thu, 19 Oct 2017 07:18:05 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: transpose

RW9 — Thu, 19 Oct 2017 08:42:57 GMT

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;

Re: transpose

Amalik — Thu, 19 Oct 2017 08:45:55 GMT

Thanks, I was just not using valid SAS name