BookmarkSubscribeRSS Feed
Chang
Quartz | Level 8

hi,

In medical journal articles, it is very common to see a big long summary statistic table that usually occupies the space of table 1. Perhaps medical readers prefer to see how a patient characteristic changes from year to year horizontally. But you know long format data is a lot easier to generate. I have managed to organise my data in the following layout, which is in a long format. All the variables have been converted to character. The length, however, varies from variable to variable. To keep this example simple, I use serial variable names. They don't look that nice in my data.

year   var1 var2 var3 var4 var5 var6..... var17

------------------------------------------------------------

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

------------------------------------------------------------

I would like to (1) reshape the long-format table to the layout below, (2) replace var1 var2... var17 with desired labels and (3) export the wide-format table to a RTF via PROC REPORT. As can be seen in the following mock table, I place years horizontally from second column to the last column. Each var become an item under the first column characteristic. I have no idea how this can be done in SAS. Most posts talk about transposing data from wide to long. Meanwhile I am also looking at the reshape2 package in R. Hope this post will benefit medical SAS programmers. Please make your solution easy to replicate.

-------------------------------------------------------------------------------

Characteristic  2004 2005 2006 2007 2008..... 2013

-------------------------------------------------------------------------------

var1

var2

var3

var4

var5

var6

...

var17

---------------------------------------------------------------------------------

I have exported my table to a CSV. I guess again it can't be uploaded here. Please download it at the bottom of the post at the URL below. My original data is in the spreadsheet 'have.' I have what I want in the spreadsheet 'want' Any advice would be highly appreciated.

Reshaping data to the table 1 in medical journal articles | Like-Minded Data Analysts

10 REPLIES 10
Reeza
Super User

I think you need to include more details, I'm having a hard time seeing what you want.

That looks like a transpose, possibly a double one. 

There are a lot of papers on generating table of characteristics from SAS datasets, search LexJansen.com for many examples and macros.

Steelers_In_DC
Barite | Level 11

I think this is what you are looking for:

data have;

infile cards missover;

input year var1-var17;

cards;

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

;

proc transpose data=have out=want (rename=(_NAME_ = Characteristic));id year;

Chang
Quartz | Level 8

hi Mark,

does your method require that a PROC transpose is run for every variable? I think var= statement can take just one variable at a time. If so, there will be 17 proc transpose. Eventually 17 out= data set need to be vertically stacked into one table.

Steelers_In_DC
Barite | Level 11

Are you able to run the code I provided and look at the output?  If so let me know if this is what you want.

Chang
Quartz | Level 8

hi Mark,

Unfortunately your code is not working on my side. I don't see var= statement in your code. Would PROC TRANSPOSE work without the statement? Fortunately I've finally reshaped my data with a do loop and a merge. I think there is no id variable in my data. I just can't get PROC TRANSPOSE work with my data.

/*reshape data from wide to long */

%macro reshape_merge;

/*add all the value variables in this list*/

    %let var_list = char_num_admi char_pct_male age_1 age_2 income_1 income_2 income_3

                            income_4 cost_1 cost_2 ccx los_1 los_2 died   

                            char_hosp_1_pct char_hosp_2_pct char_hosp_3_pct ;

/*reshape data from wide to long for each year*/

    %do year=2004 %to 2013;

        data year_&year.;

            set data_pro.keep_colon_summary_table_1_wide(where= (year= &year.) )    ;

            array wide{*} $ &var_list. ;

                do counter= 1 to dim(wide); /*create format which a counter corresponds to a patient characteristic*/

                    year&year.= wide(counter);

                    output;

                end;

            drop year &var_list. ;

        run;  

    %end;

/*merge year2004-year2013*/

    data data_pro.year2004_2013;

        merge year_2004-year_2013;

        by counter;

        format counter colon_characteristic.; /*apply format to counter*/

        rename year2013=Avg    counter= Characteristic;

    run;   

%mend reshape_merge;

%reshape_merge;

Steelers_In_DC
Barite | Level 11

Here is the output I see.  I assume we are using different versions of SAS.

Characteristic2004200520062007200820092010201120122013

var1        .   .   .   .   .   .   .   .   .   .
var2        .   .   .   .   .   .   .   .   .   .
var3        .   .   .   .   .   .   .   .   .   .
var4        .   .   .   .   .   .   .   .   .   .
var5        .   .   .   .   .   .   .   .   .   .
var6        .   .   .   .   .   .   .   .   .   .
var7        .   .   .   .   .   .   .   .   .   .
var8        .   .   .   .   .   .   .   .   .   .
var9        .   .   .   .   .   .   .   .   .   .
var10       .   .   .   .   .   .   .   .   .   .
var11       .   .   .   .   .   .   .   .   .   .
var12       .   .   .   .   .   .   .   .   .   .
var13       .   .   .   .   .   .   .   .   .   .
var14       .   .   .   .   .   .   .   .   .   .
var15       .   .   .   .   .   .   .   .   .   .
var16       .   .   .   .   .   .   .   .   .   .
var17       .   .   .   .   .   .   .   .   .   .
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Would not two proc transpose work, first one to normalise the data, second to transpose up again:

data have; 

  year=2004; var1=1; var2=4; var3=6; output;

  year=2005; var1=2; var2=8; var3=9; output;

run;

proc transpose data=have out=inter;

  by year;

  var var:;

run;

proc sort data=inter;

  by _name_ year;

run;

proc transpose data=inter out=want;

  by _name_;

  var col1;

  id year;

  idlabel year;

run;

Reeza
Super User

Mark didn't generate sample data for the var1-var17 which is why it was empty. However it should work on your actual data. You should post your actual data.

If you have a mix of char and numeric none of these may work the way you want.


data have;

array var(17) var1-var17;

do year=2004 to 2013;

do i=1 to 17;

var(i)=floor(rand('normal', 50, 3));

end;

output;

end;

drop i;

run;

proc transpose data=have out=long Prefix=Y;

id year;

run;

ballardw
Super User

I suspect that you can use a Report procedure to do this.

Something in this approach if the data is already summarized, or possibly if it isn't just request the desired common statistics:

proc tabulate data=have;

class year;

var var1 - var10;

table (var1 - var10)* sum='', year='';

run;

The quotes in the ='' are to suppress a heading. If requesting multiple statistics then not such a good idea...

Ksharp
Super User

ONE proc transpose is good enough.

OR Try IML code which could save you lots of time.

data have;
infile cards missover;
input (year var1-var17) ($);
cards;
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
;
run;
proc iml;
use have;
v='var1':'var17';
read all var v into x[r=year c=Characteristic];
close;
y=t(x);
create want from y[r=Characteristic c=year];
append from y[r=Characteristic];
close;
quit;


Xia Keshan

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
  • 10 replies
  • 838 views
  • 0 likes
  • 6 in conversation