Variables in specific order

[nickspencer]

I have a sas dateset which had to be exported in an excel sheet. But the excel output should have columns in a specific order. What can be the best way to order the variables in an specific order?

[PaigeMiller]

ods excel file="filename.Xlsx";
Proc print data=have; /* or proc report */
    Var a b c d; /* set the variable order here */
run;
ods excel close;

[Reeza]

There's a few ways, Option 3 is probably the simplest since the order only matters for presentation.

Option 1: RETAIN statement before the SET statement. 

data class;
retain Age Sex Name Weight Height;
set sashelp.class;
run;

Option 2: PROC SQL with FEEDBACK option and manual reorder

proc sql feedback;
create table class as
select * from sashelp.class;
quit;

Copy the code from the log, reorder the variables and then re-run it. 

Option 3: Use ODS Excel to PROC PRINT the results and manually control the order there. 

ods excel file='/folders/myfolders/demo.xlsx';

proc print data=sashelp.class noobs label;
var Name Age Sex Weight Height;
run;

ods excel close;

---

@nickspencer wrote:
I have a sas dateset which had to be exported in an excel sheet. But the excel output should have columns in a specific order. What can be the best way to order the variables in an specific order?

---

[AndrewHowell]

How are performing the Excel output?

• PROC EXPORT
• DATA STEP
• FILENAME & a PROC

• If Data Step, then you can define the order in the data step - that should be fairly straightforward
• Similar for FILENAME/PROC - just use the PROC-specific syntax.
• If PROC EXPORT (which is an "all-at-once" step), my suggestion would be to create a view of the source data, define the order of the variables in your view, and then PROC EXPORT the view.

[nickspencer]

I am using the proc export

[PaigeMiller]

---

@nickspencer wrote:
I am using the proc export

---

Then use one of the other methods.

[nickspencer]

I am looking to use ods and include var in proc print. How do I include sheet name and replace options in ods since I haven’t used it before?

[PaigeMiller]

---

@nickspencer wrote:
I am looking to use ods and include var in proc print. How do I include sheet name and replace options in ods since I haven’t used it before?

---

Ok, then use PROC EXPORT. The steps have been explained above.

[Patrick · Accepted solution]

---

@@nickspencer wrote:
I am looking to use ods and include var in proc print. How do I include sheet name and replace options in ods since I haven’t used it before?

---

@nickspencer

ODS EXCEL gives you a lot of control over output into Excel so it's certainly worth learning. 

http://go.documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docset...

If you have specific "How To" usage questions then just Google them. There are quite a few solutions out there.

One "downside" with ODS EXCEL: It will always fully re-create the file and you can't update, delete or add a sheet in place. 

The following code shows how to define a specific sheet name. Replace is not required as it's always gonna be a full create of the whole workbook.

ods excel file='c:\temp\test.xlsx' 
  options(autofilter='all' sheet_name='mysheet');

proc print  
  data=sashelp.class(obs=5);
  var Age Name Sex Weight Height;
run;

ods excel close;