Proc transpose duplicates

[tarheel13]

I am trying to transpose this. There are duplicates within Cardiac disorders. I read on here to make another count variable and put it in the id statement. But that didn't work for me. It is making two columns of aerel and I just need one column!

proc transpose data=temp;

by aeterm_soc count;

var count;

id aerel n;

run;

How do I just get one column for cntNot_Related? 

[PeterClemmensen]

Post usable sample data please 🙂

[tarheel13]

I put the data in a CSV.

[tarheel13]

I think I made the dataset wrong. AESEV should not be in there and removing that should take out the duplicates in cardiac disorders. 

[Reeza]

So what do you want to happen when you do have two records?
It's weird to see COUNT in the BY statement, I suspect that may not be doing what you think it's supposed to be doing. You may want to back up a few steps and explain this a bit more.

[tarheel13]

I am making AE tables like I was asking about in the other post. The duplicate was not supposed to be there. This is why I was asking the best way to count as I have columns for AESEV, and AEREL. I make separate datasets by SOC and PT. I transpose datasets for AESEV and then transpose the datasets for AEREL.The datasets were made with proc sql group by. Once I took AESEV out there were no more duplicates. 

[Reeza]

Your other post sort of implied that you already had working programs, not that you had an approach in mind. Didn't you have an indicator variable that you were using to create the summaries?

[tarheel13]

These programs are kind of a mess. You can only count once per SOC per PT per subject. Has to be by max severity and max relatedness. My program is quite lengthy so I'm pretty sure there's a better way to do this. Yes, I made one subject-level occurrence flag and I was summing that in SQL to get counts. I kind of need something that's going to work every time because I have to repeat these tables for moderate disease severity, severe disease severity, and all subjects. Additionally, they must be presented separately for treatment A and B. The approach has been to make a dataset for SOC by aesev, PT by aesev, SOC by aerel, and PT by aerel. I transpose the count and percent separately and merge it back together to create 1 dataset. Then I wrote a macro to split out separate datasets by SOC and stack them all together. I make the top row in the table with proc summary. The top row is Any SOC and Any PT.