|
|
Data Structure Using Proc Transpose
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 =3D have out =3D trans;
by pat ;
var res;
id vis;
run;
In the Trans data set the columns are not in the correct order =85 i.e
since vis 11.2 is greater than vis 3 and 4 , I would like to see
column 11.2 at the end =85 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 =85
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
|
|
0
|
|
|
|
Reply
|
 ali6058 (166)
|
5/19/2010 3:13:23 PM |
|
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_=3D1 then do;
pat=3D0; vis=3D1; output;
pat=3D0; vis=3D1.1; output;
pat=3D0; vis=3D1.2; output;
pat=3D0; vis=3D2; output;
pat=3D0; vis=3D3; output;
pat=3D0; vis=3D4; output;
pat=3D0; vis=3D11.2; output;
end;
set have;
output;
run;
proc transpose data =3D have out =3D trans (where=3D(pat^=3D0));
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=A0am, Al <ali6...@gmail.com> wrote:
> Hi All:
>
> This is the problem I am running in to when I try =A0flipping the data
> using Proc transpose
>
> data work.have;
> =A0 infile datalines truncover;
>
> =A0 input pat vis res;
>
> =A0 datalines;
> 1 =A0 =A01 =A0 =A0 =A012
> 1 =A0 =A01.1 =A0 =A013
> 1 =A0 =A01.2 =A0 =A012
> 1 =A0 =A02 =A0 =A0 =A011
> 1 =A0 =A011.2 =A0 23
> 2 =A0 =A01 =A0 =A0 =A009
> 2 =A0 =A02 =A0 =A0 =A014
> 2 =A0 =A03 =A0 =A0 =A011
> 3 =A0 =A01 =A0 =A0 =A010
> 3 =A0 =A02 =A0 =A0 =A011
> 3 =A0 =A03 =A0 =A0 =A01.1
> 3 =A0 =A04 =A0 =A0 =A010
> ;
> run;
>
> proc transpose data =3D have out =3D trans;
> =A0 =A0by pat ;
> =A0 =A0var res;
> =A0 =A0id vis;
> run;
>
> In the Trans data set =A0the columns are not =A0in the correct order =85 =
i.e
> since vis 11.2 is greater than vis 3 and 4 , I would like to see
> column 11.2 at the =A0end =85 but not in between .. I know =A0this is
> happening because of a decimal point =A0 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 =A0which =A0is painful when you have like mor=
e
> than 100 columns =85
>
> Obs =A0 =A0pat =A0 =A0_NAME_ =A0 =A0_1 =A0 =A0_1D1 =A0 =A0_1D2 =A0 =A0_2 =
=A0 =A0_11D2 =A0 =A0 _3
> _4
>
> =A01 =A0 =A0 =A01 =A0 =A0 =A0res =A0 =A0 =A012 =A0 =A0 13 =A0 =A0 =A012 =
=A0 =A0 11
> 23 =A0 =A0 =A0 . =A0 =A0 =A0.
> =A02 =A0 =A0 =A02 =A0 =A0 =A0res =A0 =A0 =A0 9 =A0 =A0 =A0. =A0 =A0 =A0 .=
=A0 =A0 14 =A0 =A0 =A0 .
> 11.0 =A0 =A0 .
> =A03 =A0 =A0 =A03 =A0 =A0 =A0res =A0 =A0 =A010 =A0 =A0 =A0. =A0 =A0 =A0 .=
=A0 =A0 11 =A0 =A0 =A0 . =A0 =A0 =A01.1
> 10
>
> Let me know if I am not being clear in explaining the issue
>
> Thanks in advance
|
|
|
0
|
|
|
|
Reply
|
 Ya
|
5/19/2010 3:43:31 PM
|
|
On May 19, 11:13=A0am, Al <ali6...@gmail.com> wrote:
> Hi All:
>
> This is the problem I am running in to when I try =A0flipping the data
> using Proc transpose
>
> data work.have;
> =A0 infile datalines truncover;
>
> =A0 input pat vis res;
>
> =A0 datalines;
> 1 =A0 =A01 =A0 =A0 =A012
> 1 =A0 =A01.1 =A0 =A013
> 1 =A0 =A01.2 =A0 =A012
> 1 =A0 =A02 =A0 =A0 =A011
> 1 =A0 =A011.2 =A0 23
> 2 =A0 =A01 =A0 =A0 =A009
> 2 =A0 =A02 =A0 =A0 =A014
> 2 =A0 =A03 =A0 =A0 =A011
> 3 =A0 =A01 =A0 =A0 =A010
> 3 =A0 =A02 =A0 =A0 =A011
> 3 =A0 =A03 =A0 =A0 =A01.1
> 3 =A0 =A04 =A0 =A0 =A010
> ;
> run;
>
> proc transpose data =3D have out =3D trans;
> =A0 =A0by pat ;
> =A0 =A0var res;
> =A0 =A0id vis;
> run;
>
> In the Trans data set =A0the columns are not =A0in the correct order =85 =
i.e
> since vis 11.2 is greater than vis 3 and 4 , I would like to see
> column 11.2 at the =A0end =85 but not in between .. I know =A0this is
> happening because of a decimal point =A0 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 =A0which =A0is painful when you have like mor=
e
> than 100 columns =85
>
> Obs =A0 =A0pat =A0 =A0_NAME_ =A0 =A0_1 =A0 =A0_1D1 =A0 =A0_1D2 =A0 =A0_2 =
=A0 =A0_11D2 =A0 =A0 _3
> _4
>
> =A01 =A0 =A0 =A01 =A0 =A0 =A0res =A0 =A0 =A012 =A0 =A0 13 =A0 =A0 =A012 =
=A0 =A0 11
> 23 =A0 =A0 =A0 . =A0 =A0 =A0.
> =A02 =A0 =A0 =A02 =A0 =A0 =A0res =A0 =A0 =A0 9 =A0 =A0 =A0. =A0 =A0 =A0 .=
=A0 =A0 14 =A0 =A0 =A0 .
> 11.0 =A0 =A0 .
> =A03 =A0 =A0 =A03 =A0 =A0 =A0res =A0 =A0 =A010 =A0 =A0 =A0. =A0 =A0 =A0 .=
=A0 =A0 11 =A0 =A0 =A0 . =A0 =A0 =A01.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 =3D "%sysfunc(pathname(WORK))\want.pdf";
proc tabulate data=3Dhave;
class pat vis;
var res;
table pat,vis*(res=3D''*min=3D'');
run;
ods pdf close;
----------
Richard A. DeVenezia
http://www.devenezia.com
|
|
|
0
|
|
|
|
Reply
|
Richard
|
5/19/2010 9:09:28 PM
|
|
On May 19, 4:09=A0pm, "Richard A. DeVenezia" <rdevene...@gmail.com>
wrote:
> On May 19, 11:13=A0am, Al <ali6...@gmail.com> wrote:
>
>
>
>
>
> > Hi All:
>
> > This is the problem I am running in to when I try =A0flipping the data
> > using Proc transpose
>
> > data work.have;
> > =A0 infile datalines truncover;
>
> > =A0 input pat vis res;
>
> > =A0 datalines;
> > 1 =A0 =A01 =A0 =A0 =A012
> > 1 =A0 =A01.1 =A0 =A013
> > 1 =A0 =A01.2 =A0 =A012
> > 1 =A0 =A02 =A0 =A0 =A011
> > 1 =A0 =A011.2 =A0 23
> > 2 =A0 =A01 =A0 =A0 =A009
> > 2 =A0 =A02 =A0 =A0 =A014
> > 2 =A0 =A03 =A0 =A0 =A011
> > 3 =A0 =A01 =A0 =A0 =A010
> > 3 =A0 =A02 =A0 =A0 =A011
> > 3 =A0 =A03 =A0 =A0 =A01.1
> > 3 =A0 =A04 =A0 =A0 =A010
> > ;
> > run;
>
> > proc transpose data =3D have out =3D trans;
> > =A0 =A0by pat ;
> > =A0 =A0var res;
> > =A0 =A0id vis;
> > run;
>
> > In the Trans data set =A0the columns are not =A0in the correct order =
=85 i.e
> > since vis 11.2 is greater than vis 3 and 4 , I would like to see
> > column 11.2 at the =A0end =85 but not in between .. I know =A0this is
> > happening because of a decimal point =A0 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 =A0which =A0is painful when you have like m=
ore
> > than 100 columns =85
>
> > Obs =A0 =A0pat =A0 =A0_NAME_ =A0 =A0_1 =A0 =A0_1D1 =A0 =A0_1D2 =A0 =A0_=
2 =A0 =A0_11D2 =A0 =A0 _3
> > _4
>
> > =A01 =A0 =A0 =A01 =A0 =A0 =A0res =A0 =A0 =A012 =A0 =A0 13 =A0 =A0 =A012=
=A0 =A0 11
> > 23 =A0 =A0 =A0 . =A0 =A0 =A0.
> > =A02 =A0 =A0 =A02 =A0 =A0 =A0res =A0 =A0 =A0 9 =A0 =A0 =A0. =A0 =A0 =A0=
. =A0 =A0 14 =A0 =A0 =A0 .
> > 11.0 =A0 =A0 .
> > =A03 =A0 =A0 =A03 =A0 =A0 =A0res =A0 =A0 =A010 =A0 =A0 =A0. =A0 =A0 =A0=
. =A0 =A0 11 =A0 =A0 =A0 . =A0 =A0 =A01.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. =A0In those cases
> you should step back and consider using Proc TABULATE or REPORT to
> output what you want. =A0Perhaps 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;
> =A0 infile datalines truncover;
> =A0 input pat vis res;
> =A0 datalines;
> 1 =A0 =A01 =A0 =A0 =A012
> 1 =A0 =A01.1 =A0 =A013
> 1 =A0 =A01.2 =A0 =A012
> 1 =A0 =A02 =A0 =A0 =A011
> 1 =A0 =A011.2 =A0 23
> 2 =A0 =A01 =A0 =A0 =A009
> 2 =A0 =A02 =A0 =A0 =A014
> 2 =A0 =A03 =A0 =A0 =A011
> 3 =A0 =A01 =A0 =A0 =A010
> 3 =A0 =A02 =A0 =A0 =A011
> 3 =A0 =A03 =A0 =A0 =A01.1
> 3 =A0 =A04 =A0 =A0 =A010
> ;
> run;
>
> ods pdf file =3D "%sysfunc(pathname(WORK))\want.pdf";
>
> proc tabulate data=3Dhave;
> =A0 class pat vis;
> =A0 var res;
> =A0 table pat,vis*(res=3D''*min=3D'');
> 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 !
|
|
|
0
|
|
|
|
Reply
|
Al
|
5/19/2010 11:28:36 PM
|
|
|
3 Replies
333 Views
(page loaded in 0.619 seconds)
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
131506 articles. 
17 followers. 