BookmarkSubscribeRSS Feed
windy
Quartz | Level 8

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).

IDtimelevelValue
ID1time100.5
ID1time110.2
ID1time120.8
ID1time201
ID1time211.3
ID1time221.4
ID1time300.9
ID1time310.7
ID1time321.3
ID2time100.7
ID2time110.4
ID2time121
ID2time201.2
ID2time211.5
ID2time221.6
ID2time301.1
ID2time310.9
ID2time321.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.

IDtimelevel0level1level2
ID1time10.50.20.8
ID1time211.31.4
ID1time30.90.71.3
ID2time10.70.41
ID2time21.21.51.6
ID2time31.10.91.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

5 REPLIES 5
ballardw
Super User

"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.

novinosrin
Tourmaline | Level 20


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;
windy
Quartz | Level 8

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.

 

 

novinosrin
Tourmaline | Level 20

That error indicates your posted sample "HAVE" isn't quite a representative of your real. Can you please review your data?

windy
Quartz | Level 8

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 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 835 views
  • 0 likes
  • 3 in conversation