topic Re: Proc transpose - multidimension in SAS Procedures

Proc transpose - multidimension

windy — Thu, 26 Nov 2020 02:37:51 GMT

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

Re: Proc transpose - multidimension

ballardw — Thu, 26 Nov 2020 02:54:33 GMT

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

Re: Proc transpose - multidimension

novinosrin — Thu, 26 Nov 2020 03:00:58 GMT



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;

Re: Proc transpose - multidimension

windy — Thu, 26 Nov 2020 03:19:00 GMT

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.

Re: Proc transpose - multidimension

novinosrin — Thu, 26 Nov 2020 04:00:58 GMT

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

Re: Proc transpose - multidimension

windy — Thu, 26 Nov 2020 05:25:48 GMT

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