How to untranspose a wide file

[#1]

You may or may not have seen the post this past August (https://communities.sas.com/t5/SAS-Procedures/Reverse-Transpose/m-p/392011 ) that asked how one can transpose a wide file back to the form it existed before it was transposed. That post motivated me to put together a team to come up with a better solution than any that have already been made public. I immediately thought of @FriedEgg and @snoopy369 to help me co-author a novel approach and, when @Rick_SAS mentioned @Gerhard_SAS's macro, I knew that with him I had the perfect team to address the question.

The four of us have created the macro I had envisioned, and the paper has been accepted for presentation at this year's (2018) SGF. The unpaid team is currently testing the code but, of course, we might be too close to the code to see any problems or oversights we hadn't considered.

That's where you (anyone and everyone in the Communities who might be interested) can help.

I've attached the current macro, and draft paper and materials. If you have any wide files, and don't mind spending a few minutes to untranspose them, we'd love to hear from you. You can reach me, directly, at art@analystfinder.com.

Of course, we'll post updates if either the paper or macro change prior to the paper's publication.

Download the paper and tip sheet here

You can also download the macro from the link at the bottom of this page.

Art, CEO, AnalystFinder.com

[Rick_SAS]

Hi Art. I'm sure your macro is fabulous. I encourage you to think about whether 'untranspose' is the best name. Many wide data sets did not start off as long data, so there is no initial transpose that you are "undoing." Furthermore, if a data set started out in long form, you do not know the details by which it was converted to wide form. You are basing your analysis on prefixes, IDs, suffixes, etc. I have not studied your code, but I guess that the code treats those quantities as coming from a single variable, which might not have been the case. Thirdly, if you want to attract traffic from internet searches, I suspect you could choose a more SEO-friendly name.

A name like 'SuperWideToLong' or 'FlexibleWideToLong' might be more accurate and better communicate the purpose of the macro.

[art297]

Rick, Thanks for your comments and, yes, we may well change the name to something more SEO friendly. As for the code, the macro uses prefixes/suffixes etc to identify variable names and ID values. The combination of task, and wide-file variable metadata, determine the variable characteristics applied to the new (i.e., long or less wide) datasets that the macro creates.