BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nickspencer
Obsidian | Level 7
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?
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@@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;

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
Reeza
Super User

 

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
Moderator

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
Obsidian | Level 7
I am using the proc export
PaigeMiller
Diamond | Level 26

@nickspencer wrote:
I am using the proc export

Then use one of the other methods.

--
Paige Miller
nickspencer
Obsidian | Level 7
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
Diamond | Level 26

@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.

--
Paige Miller
Patrick
Opal | Level 21

@@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;

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
  • 8 replies
  • 10913 views
  • 0 likes
  • 5 in conversation