topic transposing to make one record per id. in SAS Programming

transposing to make one record per id.

righcoastmike — Thu, 02 Aug 2018 14:02:39 GMT

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

Re: transposing to make one record per id.

novinosrin — Thu, 02 Aug 2018 14:16:54 GMT

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;

Re: transposing to make one record per id.

RW9 — Thu, 02 Aug 2018 14:15:50 GMT

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

Re: transposing to make one record per id.

PaigeMiller — Thu, 02 Aug 2018 14:17:42 GMT

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

Re: transposing to make one record per id.

righcoastmike — Thu, 02 Aug 2018 14:24:58 GMT

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

Re: transposing to make one record per id.

kiranv_ — Thu, 02 Aug 2018 14:31:27 GMT

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

Re: transposing to make one record per id.

ballardw — Thu, 02 Aug 2018 19:53:56 GMT

@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?