sas >> Order variables when exporting to Excel

by kevin.bladon » Fri, 22 Sep 2006 23:01:31 GMT

Hello all,

Does anyone know how to select the order that my variables (columns) will
appear in Excel when exporting from SAS?

Thanks,
Kevin Bladon

sas >> Order variables when exporting to Excel

by tobydunn » Fri, 22 Sep 2006 23:17:19 GMT


Kevin ,

Well without know how you are exporting your data set(s) it is pretty hard
to tell you exactly how you need to do what you ask. Two things in general
ome to mind:

Var statement in a procedure

And

Retain statement in your data step that creates the data.

Example:

Data One ;
A = 1 ;
B = 2 ;
C = 3 ;
Run ;

Data New ;
Retains B C A ;
Set One ;
Run ;




Toby Dunn

When everything is coming at you all at once, your in the wrong lane.

A truly happy person is someone who can smile and enjoy the scenery on a
detour.





From: Kevin Bladon < XXXX@XXXXX.COM >
Reply-To: Kevin Bladon < XXXX@XXXXX.COM >
To: XXXX@XXXXX.COM
Subject: Order variables when exporting to Excel
Date: Fri, 22 Sep 2006 11:01:31 -0400

Hello all,

Does anyone know how to select the order that my variables (columns) will
appear in Excel when exporting from SAS?

Thanks,
Kevin Bladon

sas >> Order variables when exporting to Excel

by yzg9 » Fri, 22 Sep 2006 23:17:27 GMT

Kevin,
What are you using (PROC-wise) to send you data to Excel? Generally,
select the variables explicitly in a PROC PRINT, REPORT, TABULATE, etc..
and use ODS HTML, Tagsets, etc. to send to Excel.

I.e.:
Ods html file='someplace.somefile.xls' style=minimal;
Proc print;
var v1 v2 f3 f4;
Run;
Ods html close;

John Gerstle, MS
Biostatistician
Northrop Grumman
CDC Information Technological Support Contract (CITS)
NCHSTP \DHAP \HICSB \Research, Analysis, and Evaluation Section
Centers for Disease Control and Prevention

"Boss. We've got cats." "Meow"

"All truth passes through three stages:
First, it is ridiculed;
Second, it is violently opposed;
Third, it is accepted as being self-evident."
- Arthur Schopenhauer (1830)


[mailto: XXXX@XXXXX.COM ]
will

sas >> Order variables when exporting to Excel

by kevin.bladon » Sat, 23 Sep 2006 00:06:28 GMT

Hello all,

Thanks for the replies... the previous methods work on their own. However, is it possible to re-order the output variables to Excel using PROC
EXPORT. For example... I would like the following data to be exported to Excel , but in the order age, ht.

data one;
input id ht age;
cards;
1 22 62
2 22 72
3 22 82
4 33 76
5 33 66
6 44 78
;
run;

PROC EXPORT DATA= One
OUTFILE='C:\Documents and Settings\test.xls'|OUTTABLE='Summary'
DBMS=EXCEL;

Thanks in advance!
Kevin.



>Kevin ,
>
>Well without know how you are exporting your data>set(s) it is pretty hard
>to tell you exactly how you need to do what you a>k. Two things >n >eneral
>ome to mind:
>
>Var>st>temen> i> a procedure
>
>And
>
>Retain statement in your data st>p >hat create> t>e data.
>
>Data >ne ;
>A > 1 ;
>> =>2 ;
>C = 3 >
>Run ;
>
>Dat> New ;
>Re>ains B > A>; >>S>t >ne ;
>Run >
>
>
>
>Toby Dunn
>
>When everything is coming at you all at once> y>ur in the wrong lane.
>
>A truly happy person is someone who can smile a>d enjoy t>e >ce>er> o> a> >detour.
>
>
>
>
>From: Kevin Bladon> >Reply-To: Kevin Bladon
>T>: XXXX@XXXXX.COM
>Subject: Order variable> when exporting to Excel
>Date: Fri, 2> S>p 2006 11:01>31>-0400
>
>Hello all,
>
>Does anyone know how to select the order that my>variables (columns) will
>appear in Excel>wh>n exporti>g from SAS?
>> >>hanks,
>Kevin Bladon
>
>


Kevin Bladon, Ph.D., A.Ag.
Resource Analyst
Silvacom Ltd.
3825 - 93 Street
Edmonton, AB
T6E 5K5
Phone: 780.462.3238
Fax: 780.462.4726
E-mail: XXXX@XXXXX.COM
www.silvacom.com

"Make every obstacle an opportunity."
- Lance Armstrong
Cancer survivor and 7 time Tour de France champion (1999-2005)

sas >> Order variables when exporting to Excel

by yzg9 » Sat, 23 Sep 2006 00:11:47 GMT

would guess the answer is no. PROC EXPORT/IMPORT are
moving/reading/converting/etc. datasets from one package to another.
The other methods mentioned are CREATING a dataset/output, so you have
more flexibility when creating the dataset/output file. The order of
variables in a dataset is irrelevant to analyses. The order only
benefits us humans reading the dataset/output file.

John Gerstle, MS
Biostatistician
Northrop Grumman
CDC Information Technological Support Contract (CITS)
NCHSTP \DHAP \HICSB \Research, Analysis, and Evaluation Section
Centers for Disease Control and Prevention

"Boss. We've got cats." "Meow"

"All truth passes through three stages:
First, it is ridiculed;
Second, it is violently opposed;
Third, it is accepted as being self-evident."
- Arthur Schopenhauer (1830)


[mailto: XXXX@XXXXX.COM ]
using
to
on a
will
France

sas >> Order variables when exporting to Excel

by tobydunn » Sat, 23 Sep 2006 00:20:04 GMT

Kevin ,

Create a view:

Data OneA / View = OneA ;
Retain C B D A ;
Set One ;
Run ;





Toby Dunn

When everything is coming at you all at once, your in the wrong lane.

A truly happy person is someone who can smile and enjoy the scenery on a
detour.





From: Kevin Bladon < XXXX@XXXXX.COM >
To: toby dunn < XXXX@XXXXX.COM >
CC: XXXX@XXXXX.COM , XXXX@XXXXX.COM
Subject: RE: Order variables when exporting to Excel
Date: Fri, 22 Sep 2006 10:06:28 -0600 (MDT)

Hello all,

Thanks for the replies... the previous methods work on their own. However,
is it possible to re-order the output variables to Excel using PROC
EXPORT. For example... I would like the following data to be exported to
Excel , but in the order age, ht.

data one;
input id ht age;
cards;
1 22 62
2 22 72
3 22 82
4 33 76
5 33 66
6 44 78
;
run;

PROC EXPORT DATA= One
OUTFILE='C:\Documents and Settings\test.xls'|OUTTABLE='Summary'
DBMS=EXCEL;

Thanks in advance!
Kevin.



>Kevin ,
>
>Well without know how you are exporting your data >et(s) it is pretty hard
>to tell you exactly how you need to do what you as>. Two things in>gen>ral
>ome to mind:
>
>Var >tat>ment i> a >rocedure
>
>And
>
>Retain statement in your data ste> th>t creates t>e d>ta.
>
>Ex>mple:
> > >Data One>;
>A = 1>;
>B =>2 ;> >C = 3 ;
>Run ;
>
>Data >ew ;
>Reta>ns B C A>;
> > > > >
>
>Toby Dunn
>
>When everything is coming at you all at once,>you> in the wrong lane.
>
>A truly happy person is someone who can smile an> enjoy the>sce>ery>on >
>det>ur.
>
>
>
>
>From: Kevin Bladon
>To> XXXX@XXXXX.COM
>Subject: Order variables>when exporting to Excel
>Date: Fri, 22>Sep>2006 11:01:31>-04>0
>
>Hello all,
>
>Does anyone know how to select the order that my >ariables (columns) will
>appear in Excel >hen>exporting >rom SAS?
>
>Tha>ks,
>Kevin Bladon
>
>


Kevin Bladon, Ph.D., A.Ag.
Resource Analyst
Silvacom Ltd.
3825 - 93 Street
Edmonton, AB
T6E 5K5
Phone: 780.462.3238
Fax: 780.462.4726
E-mail: XXXX@XXXXX.COM
www.silvacom.com

"Make every obstacle an opportunity."
- Lance
Armstrong
Cancer
survivor and 7 time Tour de France champion (1999-2005)

Similar Threads

1. Controlling variable order in exporting data

2. export re-order input fields to export file [csv]

I've seen it, used it but can't find it within Excel.

X-porting data [defined area on sheet] to a *.csv file.
Re-order of fields from input file to output file - which will be imported 
into another data base program.
import    export  fields
1              3
2              1
3              2
----------
Have seen screen that has imp column (source) on left and output column on 
right [no data] 
Select one item from left and move to right in order desired for output.
also have seen choice for move up/down on the export column. (change order 
of output)
Any/all help appreciated.
madisonPete

3. Exporting from Access to Excel columns are in wrong order - External Data - Access

4. Export to excel and keep the same order as in access

When I export to excel from Access 2003 I receive the records in Excel in a 
different order then it was in access How do I maintain Order ??? 

5. Order of Data export to Excel

6. Columns are not in the same order after export to excel

I have a question about reports in access.
I created a simple report in access that looks great in word (exported as 
snp or rtf)
but when I export the same report into excel - the columns are not in the 
same order. They are all shuffled.
Is there a control in the report that would set the order?
does someone know if this is possible?
thank you

7. Column order disrupted on export to Excel

8. Exporting to Excel and Column Order

Hi,

I have a DataGrid, whose sourceI am exporting to Excel.  This works fine
except for the Column ordering.  My datasource is not a datatable, with a 
typical
SELECT statement where I can select the column orders.  Instead the datasource
is a class which implements IList, containing a collection of my data.  My 
problem
again is that I don't know how to control the order of the columns that are 
exported.

Any Ideas?

Thanks