DATA Step, Macro, Functions and more

transpose

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

transpose

Hi,

 

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

 

 Before  
ID198519861987
1345
2456
3567

 

 After 
119863
119874
119885
219864
219875
219886
319865
319876
31988

7

 

 

Suggestions, please.

Cheers,

AMalik


Accepted Solutions
Solution
‎10-19-2017 04:45 AM
Trusted Advisor
Posts: 1,571

Re: transpose

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.

View solution in original post


All Replies
Solution
‎10-19-2017 04:45 AM
Trusted Advisor
Posts: 1,571

Re: transpose

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.

Contributor
Posts: 24

Re: transpose

Thanks, I was just not using valid SAS name

Super User
Posts: 7,809

Re: transpose

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,970

Re: transpose

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 93 views
  • 0 likes
  • 4 in conversation