Hi there,
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).
ID | time | level | Value |
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 |
What I want to do is to transpose this table so that for each ID at each time period, there is only 1 row.
ID | time | level0 | level1 | level2 |
ID1 | time1 | 0.5 | 0.2 | 0.8 |
ID1 | time2 | 1 | 1.3 | 1.4 |
ID1 | time3 | 0.9 | 0.7 | 1.3 |
ID2 | time1 | 0.7 | 0.4 | 1 |
ID2 | time2 | 1.2 | 1.5 | 1.6 |
ID2 | time3 | 1.1 | 0.9 | 1.5 |
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;
VAR value;
ID _level_;
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.
Windy
"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.
That error indicates your posted sample "HAVE" isn't quite a representative of your real. Can you please review your data?
Hi,
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.
Updated:
After I delete duplicates, my original code does work. Thank you so much for your support @novinosrin
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.