I am struggling with Proc transpose.
Here is what the data looks like. For each ID at each time period, there are three observations (3 rows).
What I want to do is to transpose this table so that for each ID at each time period, there is only 1 row.
I tried the following codes, but it's not working.
PROC SORT DATA=have; BY id time; RUN;
PROC TRANSPOSE data=have out=want;
BY id time;
IDLABEL _level_; RUN;
Please help me to solve this problem. My goal is to create a column, let's say probability = value1/(1+value0 + value1 + value2). The probability is calculated for each ID at each time period.
Thank you so much in advance.
"Not working" is awful vague.
Are there errors in the log?: Post the code and log in a code box opened with the </> icon to maintain formatting of error messages.
No output? Post any log in a code box.
Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.
data have; input (ID time) ($) level Value; cards; ID1 time1 0 0.5 ID1 time1 1 0.2 ID1 time1 2 0.8 ID1 time2 0 1 ID1 time2 1 1.3 ID1 time2 2 1.4 ID1 time3 0 0.9 ID1 time3 1 0.7 ID1 time3 2 1.3 ID2 time1 0 0.7 ID2 time1 1 0.4 ID2 time1 2 1 ID2 time2 0 1.2 ID2 time2 1 1.5 ID2 time2 2 1.6 ID2 time3 0 1.1 ID2 time3 1 0.9 ID2 time3 2 1.5 ; proc transpose data=have out=want(drop=_:) prefix=level; by id time; var value; id level; run;
Hi @ballardw, sorry for that, but the link in your reply does not exist. I hope the error message I post in the reply is what it should be.
This is the error I got when I used my code as well as @novinosrin code:
ERROR: The ID value "_0" occurs twice in the same BY group. ERROR: The ID value "_1" occurs twice in the same BY group. ERROR: The ID value "_2" occurs twice in the same BY group.
I'm checking for the duplicate ID in my sample and then I run your suggested code again to see what happens. Thanks for your reply.
After I delete duplicates, my original code does work. Thank you so much for your support @novinosrin
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.