# Transposing Every nth row to column in a large dataset

I am attempting to work with a very large data-set (~1.5mil lines) for the first time in SAS and I am having some difficulty. The data-set I have is formatted as a "long" .txt file as follows:

'cat1/: Topic1_Variable1'
'cat2/: Topic1_Variable2'
'cat3/: Topic1_Variable3'
'cat4/: Topic1_Variable4'

'cat1/: Topic2_Variable1'
'cat2/: Topic2_Variable2'
'cat3/: Topic2_Variable3'
'cat4/: Topic2_Variable4'

'cat1/: Topic3_Variable1'
'cat2/: Topic3_Variable2'
'cat3/: Topic3_Variable3'
'cat4/: Topic3_Variable4'
...


Fro analysis and sharing with others, I really would like to see it formatted as follows:

cat1              cat2              cat3              cat4
Topic1_Variable1  Topic1_Variable2  Topic1_Variable3  Topic1_Variable4
Topic2_Variable1  Topic2_Variable2  Topic2_Variable3  Topic2_Variable4
Topic3_Variable1  Topic3_Variable2  Topic3_Variable3  Topic3_Variable4


I think that this may be easier in R, but I honestly am drawing a complete blank in SAS. I've even played with MS Access to try to get it to look the way I want but the program crashes every time (due to the size?). At any rate, I have looked into some of the statements in PROC TRANSPOSE and PROC SQL but it seems that most functions within those procedures are utilized to combine duplicate 'Topics'. In the data I have been provided, each "group" represents an individual response to a question with several thousand individuals repeated, I want to retain the independence of each occurrence and not perform a UNION as defined in PROC SQL. At this point, I feel like I am over-thinking this but I just can't get around the mental block and actually do what I am working toward. Any help or guidance is much appreciated. I'm open to trying all suggestions or ideas, I think I have access to most statistical computing programs.

• What software can you use to solve this? Are you limited to SAS? – MikeRSpencer Aug 9 '15 at 13:30
• Might be a better fit on StackOverflow. This is just data formatting. What dev tools are comfortable with. I am surprised Access is crashing at 1.5 million lines. – paparazzo Dec 8 '15 at 9:22

Hey is Python or other tools an option here? Since you mentioned it is a large dataset, you might want to iterate over it instead of loading all of them at once.

Here is a solution in Python:

import pandas as pd
from collections import defaultdict

inputs = [
'cat1/: Topic1_Variable1',
'cat2/: Topic1_Variable2',
'cat3/: Topic1_Variable3',
'cat4/: Topic1_Variable4',
'cat1/: Topic2_Variable1',
'cat2/: Topic2_Variable2',
'cat3/: Topic2_Variable3',
'cat4/: Topic2_Variable4',
'cat1/: Topic3_Variable1',
'cat2/: Topic3_Variable2',
'cat3/: Topic3_Variable3',
'cat4/: Topic3_Variable4',]

outputs = defaultdict(list)

for item in inputs:
cat, topic = item.split('/: ')
outputs[cat].append(topic)

print pd.DataFrame(outputs)


Output:

               cat1              cat2              cat3              cat4
0  Topic1_Variable1  Topic1_Variable2  Topic1_Variable3  Topic1_Variable4
1  Topic2_Variable1  Topic2_Variable2  Topic2_Variable3  Topic2_Variable4
2  Topic3_Variable1  Topic3_Variable2  Topic3_Variable3  Topic3_Variable4


In R I would strongly suggest the reshape2 package, in particular the cast / melt combo of functions.

Been a while since I used SAS but I think you could use a data step where you make an ID var on which you can aggregate, then vars for each "cat1-4." You could then use proc transpose or do a proc SQL sum() with a "groupby" statement on the ID variable.

So first step is to get to:

'Topic_ID' | 'Cat1' | 'Cat2' | 'Cat3' | 'Cat4'
1     |   1.5  |    0   |    0   |    0
1     |    0   |    3   |    0   |    0
1     |    0   |    0   |    1   |    0
1     |    0   |    0   |    0   |    4
2     |    3   |    0   |    0   |    0
...


If Topic_number isn't explicit in your dataset, you can always calculate it for each observation by floor(obs_#/4).

Then by performing a Proc SQL sum() using group by, you can reduce the data to look like this

'Topic_ID' | 'Cat1' | 'Cat2' | 'Cat3' | 'Cat4'
1     |   1.5  |    3   |    1   |    4
2     |    3   |   ...  |  ...   |  ...


This isn't necessarily the most efficient method, but it's simple to implement with SAS.