BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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 

 

 

       

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
novinosrin
Tourmaline | Level 20
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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

righcoastmike
Quartz | Level 8

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 

 

PaigeMiller
Diamond | Level 26

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
kiranv_
Rhodochrosite | Level 12

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
ballardw
Super User

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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