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
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!).
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;
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!).
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
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).
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
@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?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.