Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need to reformat a data set using SAS

Need to reformat a data set using SAS

Need to reformat a data set using SAS


I have a dataset that looks something like this;

Patient  Visit#  Sex Blood  Urine
1000      01      M
1000      02          4.5    5.2
1000      03          4.2    5.1
1001      01      F
1001      02          3.5    6.2
1001      03          3.2    5.3

I would like to have it look like this:

Patient   Sex    Blood01   Urine01   Blood02   Urine02
1000       M     4.5         5.2      4.2       5.1
1001       F     3.5         6.2      3.2        5.3     

This difference here is that I want to see one row per patient (with their blood/ urine results by visit code across the columns).

I'm only somewhat familiar with SAS macros-- and I think that's what I need to solve this problem, but I'm not sure where to start. Any help would be greatly appreciated-- or event just some pointers to get me going in the write direction.

Thank you!

RE: Need to reformat a data set using SAS


There may be an easier way to do it, but one method is using proc transpose. You'd have to play around with the sex field first - i'd probably remove that row, sort out the rest and then add it back in.

But if you assume you start off with the following dataset called "test":

Patient    VisitNum    Blood    Urine
1000    1    4.5    5.2
1000    2    4.2    5.1
1001    1    3.5    6.2
1001    2    3.2    5.3

You can run the following code:

data test;
set test;
proc transpose data=test out=testblood(drop=_name_ _label_);
by patient;
id bloodnum;
var blood;
proc transpose data=test out=testurine(drop=_name_ _label_);
by patient;
id urinenum;
var urine;

proc sql;
create table final
as select a.*,b.*
from testblood a
left join testurine b
on a.patient=b.patient;

Not sure if that helps?  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close