Prev: Calculate Percent using proc report
Next: SAS on iMAC/MacPro
From: Al on 19 May 2010 11:13

Hi All:


This is the problem I am running in to when I try flipping the data
using Proc transpose


data work.have;
infile datalines truncover;


input pat vis res;


datalines;
1 1 12
1 1.1 13
1 1.2 12
1 2 11
1 11.2 23
2 1 09
2 2 14
2 3 11
3 1 10
3 2 11
3 3 1.1
3 4 10
;
run;



proc transpose data = have out = trans;
by pat ;
var res;
id vis;
run;


In the Trans data set the columns are not in the correct order … i.e
since vis 11.2 is greater than vis 3 and 4 , I would like to see
column 11.2 at the end … but not in between .. I know this is
happening because of a decimal point for some the vis values ..

I am presenting one record per pat to the review team in excel spread
sheet .. so when I export them to excel ,I have to manually adjust the
columns in the proper order which is painful when you have like more
than 100 columns …


Obs pat _NAME_ _1 _1D1 _1D2 _2 _11D2 _3
_4

1 1 res 12 13 12 11
23 . .
2 2 res 9 . . 14 .
11.0 .
3 3 res 10 . . 11 . 1.1
10




Let me know if I am not being clear in explaining the issue


Thanks in advance
From: Ya on 19 May 2010 11:43
The column order are determined by the order of id var value in the
original data. 11.2 is the 4th one, so it is placed in 4th column.
The following is one way you can get what you want. The idea is to add
some records at the beginning, where the id var is ordered
correctly. These records are removed later:

data have;
if _n_=1 then do;
pat=0; vis=1; output;
pat=0; vis=1.1; output;
pat=0; vis=1.2; output;
pat=0; vis=2; output;
pat=0; vis=3; output;
pat=0; vis=4; output;
pat=0; vis=11.2; output;
end;
set have;
output;
run;

proc transpose data = have out = trans (where=(pat^=0));
by pat ;
var res;
id vis;
run;

proc print;
run;

pat _NAME_ _1 _1D1 _1D2 _2 _3 _4 _11D2

1 res 12 13 12 11 . . 23
2 res 9 . . 14 11.0 . .
3 res 10 . . 11 1.1 10 .

Ya


On May 19, 8:13 am, Al <ali6...(a)gmail.com> wrote:
> Hi All:
>
> This is the problem I am running in to when I try  flipping the data
> using Proc transpose
>
> data work.have;
>   infile datalines truncover;
>
>   input pat vis res;
>
>   datalines;
> 1    1      12
> 1    1.1    13
> 1    1.2    12
> 1    2      11
> 1    11.2   23
> 2    1      09
> 2    2      14
> 2    3      11
> 3    1      10
> 3    2      11
> 3    3      1.1
> 3    4      10
> ;
> run;
>
> proc transpose data = have out = trans;
>    by pat ;
>    var res;
>    id vis;
> run;
>
> In the Trans data set  the columns are not  in the correct order … i.e
> since vis 11.2 is greater than vis 3 and 4 , I would like to see
> column 11.2 at the  end … but not in between .. I know  this is
> happening because of a decimal point   for some the vis values ..
>
> I am presenting one record per pat to the review team in excel spread
> sheet .. so when I export them to excel ,I have to manually adjust the
> columns in the proper order  which  is painful when you have like more
> than 100 columns …
>
> Obs    pat    _NAME_    _1    _1D1    _1D2    _2    _11D2     _3
> _4
>
>  1      1      res      12     13      12     11
> 23       .      .
>  2      2      res       9      .       .     14       .
> 11.0     .
>  3      3      res      10      .       .     11       .      1.1
> 10
>
> Let me know if I am not being clear in explaining the issue
>
> Thanks in advance

From: Richard A. DeVenezia on 19 May 2010 17:09
On May 19, 11:13 am, Al <ali6...(a)gmail.com> wrote:
> Hi All:
>
> This is the problem I am running in to when I try  flipping the data
> using Proc transpose
>
> data work.have;
>   infile datalines truncover;
>
>   input pat vis res;
>
>   datalines;
> 1    1      12
> 1    1.1    13
> 1    1.2    12
> 1    2      11
> 1    11.2   23
> 2    1      09
> 2    2      14
> 2    3      11
> 3    1      10
> 3    2      11
> 3    3      1.1
> 3    4      10
> ;
> run;
>
> proc transpose data = have out = trans;
>    by pat ;
>    var res;
>    id vis;
> run;
>
> In the Trans data set  the columns are not  in the correct order … i.e
> since vis 11.2 is greater than vis 3 and 4 , I would like to see
> column 11.2 at the  end … but not in between .. I know  this is
> happening because of a decimal point   for some the vis values ..
>
> I am presenting one record per pat to the review team in excel spread
> sheet .. so when I export them to excel ,I have to manually adjust the
> columns in the proper order  which  is painful when you have like more
> than 100 columns …
>
> Obs    pat    _NAME_    _1    _1D1    _1D2    _2    _11D2     _3
> _4
>
>  1      1      res      12     13      12     11
> 23       .      .
>  2      2      res       9      .       .     14       .
> 11.0     .
>  3      3      res      10      .       .     11       .      1.1
> 10
>
> Let me know if I am not being clear in explaining the issue

Many times people transpose data in order to print it. In those cases
you should step back and consider using Proc TABULATE or REPORT to
output what you want. Perhaps you will not need those extra steps
that create dynamic data structures.

In this sample code TABULATE automatically sorts the vis across each
patient row.

----------
data work.have;
infile datalines truncover;
input pat vis res;
datalines;
1 1 12
1 1.1 13
1 1.2 12
1 2 11
1 11.2 23
2 1 09
2 2 14
2 3 11
3 1 10
3 2 11
3 3 1.1
3 4 10
;
run;

ods pdf file = "%sysfunc(pathname(WORK))\want.pdf";

proc tabulate data=have;
class pat vis;
var res;
table pat,vis*(res=''*min='');
run;

ods pdf close;
----------

Richard A. DeVenezia
http://www.devenezia.com
From: Al on 19 May 2010 19:28
On May 19, 4:09 pm, "Richard A. DeVenezia" <rdevene...(a)gmail.com>
wrote:
> On May 19, 11:13 am, Al <ali6...(a)gmail.com> wrote:
>
>
>
>
>
> > Hi All:
>
> > This is the problem I am running in to when I try  flipping the data
> > using Proc transpose
>
> > data work.have;
> >   infile datalines truncover;
>
> >   input pat vis res;
>
> >   datalines;
> > 1    1      12
> > 1    1.1    13
> > 1    1.2    12
> > 1    2      11
> > 1    11.2   23
> > 2    1      09
> > 2    2      14
> > 2    3      11
> > 3    1      10
> > 3    2      11
> > 3    3      1.1
> > 3    4      10
> > ;
> > run;
>
> > proc transpose data = have out = trans;
> >    by pat ;
> >    var res;
> >    id vis;
> > run;
>
> > In the Trans data set  the columns are not  in the correct order … i.e
> > since vis 11.2 is greater than vis 3 and 4 , I would like to see
> > column 11.2 at the  end … but not in between .. I know  this is
> > happening because of a decimal point   for some the vis values ..
>
> > I am presenting one record per pat to the review team in excel spread
> > sheet .. so when I export them to excel ,I have to manually adjust the
> > columns in the proper order  which  is painful when you have like more
> > than 100 columns …
>
> > Obs    pat    _NAME_    _1    _1D1    _1D2    _2    _11D2     _3
> > _4
>
> >  1      1      res      12     13      12     11
> > 23       .      .
> >  2      2      res       9      .       .     14       .
> > 11.0     .
> >  3      3      res      10      .       .     11       .      1.1
> > 10
>
> > Let me know if I am not being clear in explaining the issue
>
> Many times people transpose data in order to print it.  In those cases
> you should step back and consider using Proc TABULATE or REPORT to
> output what you want.  Perhaps you will not need those extra steps
> that create dynamic data structures.
>
> In this sample code TABULATE automatically sorts the vis across each
> patient row.
>
> ----------
> data work.have;
>   infile datalines truncover;
>   input pat vis res;
>   datalines;
> 1    1      12
> 1    1.1    13
> 1    1.2    12
> 1    2      11
> 1    11.2   23
> 2    1      09
> 2    2      14
> 2    3      11
> 3    1      10
> 3    2      11
> 3    3      1.1
> 3    4      10
> ;
> run;
>
> ods pdf file = "%sysfunc(pathname(WORK))\want.pdf";
>
> proc tabulate data=have;
>   class pat vis;
>   var res;
>   table pat,vis*(res=''*min='');
> run;
>
> ods pdf close;
> ----------
>
> Richard A. DeVeneziahttp://www.devenezia.com- Hide quoted text -
>
> - Show quoted text -

Thank you so much to both of you!!!.. Both methods seem to work
perfectly for my scenario .... Learned new things today .. Awesome !

 | 
Pages: 1
Prev: Calculate Percent using proc report
Next: SAS on iMAC/MacPro