proc transpose

[ykoba]

Hello,

Thank you in advance for your help.

I have the following data set.

 ID date bp time

1 10/2/2018 100 0

1 10/2/2018 90 1

1 9/7/2017 89 0

1 9/7/2017 94 1

1 9/72017 89 2

2 4/4/2016 130 0

2/ 4/4/2016 145 1

The expected dataset is as follows, 

id date x0 x1 x2

1 10/2/2018 100 90

1 9/7/2017 89 94 89

2 4/4/2016 130 45

My current program is as follows,

 proc transpose data=have out=want. prefix=x;
var bp;

by id date;
id time;

I want to transpose bp for each id and date but I could not obtain the expected dataset with this codes.

I have spent a lot of time, but I haven't reached the answer.

[FreelanceReinh · Accepted solution]

Hello @ykoba,

With only a few modifications your code works well:

proc transpose data=have out=want(drop=_:) prefix=x;
var bp;
by id date notsorted;
id time;
run;

(Note: The "45" for ID 2 in your "expected dataset" should read "145".)

The NOTSORTED option of the BY statement is useful if the input dataset is not sorted by the BY variables, but the BY groups are blocks of consecutive observations.

[ykoba]

I appreciate your help and the code works!

[VDD]

@ykoba 

 data have;
input ID date mmddyy10. bp: 8. time: 8.;
cards;
1 10/2/2018 100 0
1 10/2/2018 90 1
1 9/7/2017  89 0
1 9/7/2017  94 1
1 9/7/2017  89 2
2 4/4/2016  130 0
2 4/4/2016  145 1
;
 
proc sort data=have;
	by id date;
run;
proc transpose data=have out=want(drop=_name_) prefix=x ;
	id time;
	by id date;
	var bp;
run;

the incoming dates were incorrect they needed fixing missing / 

[ykoba]

Thank for your help!