transposing to make one record per id.

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

transposing to make one record per id.

[ Edited ]

Hi everyone, I have a dataset that looks roughly like this: 

 

Data have;
Input ID Year Num_visits;
datalines;
1 2011 3
. 2012 2
. 2013 0
. 2014 4
2 2011 2
. 2012 3
. 2013 1
. 2014 0
3 2011 6
. 2012 0
. 2013 2
. 2014 5
;
run;

What I'm trying to do is convert that into a dataset that looks like this: 

 

Patient ID      2011     2012     2013     2014   Total

      1                  3         2           0           4         9

      2                  2         3           1           0         6

      3                  6         0           2           5         13

 

Any thoughts would be much appreciated! 

 

Thanks so much

 

Mike 

 

 

       


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 9,813

Re: transposing to make one record per id.

Posted in reply to righcoastmike

Well, the simple answer is don't.  First off transposed datasets are far more difficult to program with.  

Now you may want this for an output pdf or something - which is the only place transposed datasets are more useful is to the viewer - so first you need to populate id across records:

data want;
  set have (rename=(id=old_id));
  retain id;
  if old_id ne . then id=old_id;
run;

Next, proc transpose:

proc transpose data=want out=want2;
  by id;
  var num_visits;
  id year;
run;

Then add your total in a datastep.  You may also be able to use tabulate procedure to get the output.

I would still suggest you do not transpose unless needed to for an output, work with what you have (and always populate id!).

View solution in original post


All Replies
Super User
Posts: 2,049

Re: transposing to make one record per id.

[ Edited ]
Posted in reply to righcoastmike
Data have;
Input ID Year Num_visits;
datalines;
1 2011 3
 . 2012 2
 . 2013 0
 . 2014 4 
2 2011 2
 . 2012 3
 . 2013 1
 . 2014 0
3 2011 6
 . 2012 0
 . 2013 2
 . 2014 5 
;
run;

data _have;
set have;
retain _id;
if id then _id=id;
else id=_id;
drop _id;
run;

proc transpose data=_have out=want prefix=year;
by id;
id year;
var num_visits;
run;
Solution
2 weeks ago
Super User
Super User
Posts: 9,813

Re: transposing to make one record per id.

Posted in reply to righcoastmike

Well, the simple answer is don't.  First off transposed datasets are far more difficult to program with.  

Now you may want this for an output pdf or something - which is the only place transposed datasets are more useful is to the viewer - so first you need to populate id across records:

data want;
  set have (rename=(id=old_id));
  retain id;
  if old_id ne . then id=old_id;
run;

Next, proc transpose:

proc transpose data=want out=want2;
  by id;
  var num_visits;
  id year;
run;

Then add your total in a datastep.  You may also be able to use tabulate procedure to get the output.

I would still suggest you do not transpose unless needed to for an output, work with what you have (and always populate id!).

Contributor
Posts: 49

Re: transposing to make one record per id.

Thanks everyone, and your warning about transposing is well taken. 

 

At this point it's just a clearer way for me to get a sense of what I'm looking at.  I have a huge dataset so 5 records per person get's very cumbersome very quickly. 

 

As always, you have been a huge help. 

 

thanks again. 

 

Mike 

 

Respected Advisor
Posts: 3,257

Re: transposing to make one record per id.

Posted in reply to righcoastmike

If you want a table, then PROC REPORT will make a table like this with ease.

 

If you want a data set for analysis, most cases transposing your data to have variable names 2011, 2012, etc. is not a good idea (and furthermore variable names can't begin with a digit).

--
Paige Miller
PROC Star
Posts: 549

Re: transposing to make one record per id.

Posted in reply to righcoastmike

something like below.

run;

Data have(rename=(newid=id));
retain newid;
Input ID Year Num_visits;
if id ne . then newid = id;
drop id;
datalines;
1 2011 3
 . 2012 2
 . 2013 0
 . 2014 4 
2 2011 2
 . 2012 3
 . 2013 1
 . 2014 0
3 2011 6
 . 2012 0
 . 2013 2
 . 2014 5 
;

proc transpose data= have out=have1(drop=_name_);
by id;
id year;
var num_visits;
run;

data want;
set have1;
total =sum('2011'n,'2012'n,'2013'n,'2014'n);
run
Super User
Posts: 13,909

Re: transposing to make one record per id.

Posted in reply to righcoastmike

@righcoastmike wrote:

Hi everyone, I have a dataset that looks roughly like this: 

 

Data have;
Input ID Year Num_visits;
datalines;
1 2011 3
. 2012 2
. 2013 0
. 2014 4
2 2011 2
. 2012 3
. 2013 1
. 2014 0
3 2011 6
. 2012 0
. 2013 2
. 2014 5
;
run;

What I'm trying to do is convert that into a dataset that looks like this: 

 

Patient ID      2011     2012     2013     2014   Total

      1                  3         2           0           4         9

      2                  2         3           1           0         6

      3                  6         0           2           5         13

 

Any thoughts would be much appreciated! 

 

Thanks so much

 

Mike 

 

 

       


 

Big red flag: Any variable such as ID that should be identifying every record and has missing values will come back at some time in the future and cause problems. One of the first things for data quality purposes is to ensure such things. Imagine a bank trying to add/subtract money in an account without the account id?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 86 views
  • 0 likes
  • 6 in conversation