DATA Step, Macro, Functions and more

Variables in specific order

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Variables in specific order

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?

Accepted Solutions
Solution
‎05-28-2018 11:00 PM
Respected Advisor
Posts: 4,736

Re: Variables in specific order

Posted in reply to nickspencer

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


All Replies
Respected Advisor
Posts: 3,058

Re: Variables in specific order

[ Edited ]
Posted in reply to nickspencer
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
Super User
Posts: 23,771

Re: Variables in specific order

Posted in reply to nickspencer

 

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?

 

Moderator
Posts: 327

Re: Variables in specific order

Posted in reply to nickspencer

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.
Occasional Contributor
Posts: 18

Re: Variables in specific order

Posted in reply to nickspencer
I am using the proc export
Respected Advisor
Posts: 3,058

Re: Variables in specific order

Posted in reply to nickspencer

@nickspencer wrote:
I am using the proc export

Then use one of the other methods.

--
Paige Miller
Occasional Contributor
Posts: 18

Re: Variables in specific order

Posted in reply to 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?
Respected Advisor
Posts: 3,058

Re: Variables in specific order

Posted in reply to nickspencer

@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
Solution
‎05-28-2018 11:00 PM
Respected Advisor
Posts: 4,736

Re: Variables in specific order

Posted in reply to nickspencer

@@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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 181 views
  • 0 likes
  • 5 in conversation