topic Re: Variables in specific order in SAS Programming

Variables in specific order

nickspencer — Tue, 29 May 2018 01:03:45 GMT

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?

Re: Variables in specific order

PaigeMiller — Tue, 29 May 2018 01:13:39 GMT

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;

Re: Variables in specific order

Reeza — Tue, 29 May 2018 01:18:31 GMT

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?

Re: Variables in specific order

AndrewHowell — Tue, 29 May 2018 01:20:18 GMT

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.

Re: Variables in specific order

nickspencer — Tue, 29 May 2018 01:33:37 GMT

I am using the proc export

Re: Variables in specific order

PaigeMiller — Tue, 29 May 2018 01:38:09 GMT

@nickspencer wrote:
I am using the proc export

Then use one of the other methods.

Re: Variables in specific order

nickspencer — Tue, 29 May 2018 01:54:29 GMT

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?

Re: Variables in specific order

PaigeMiller — Tue, 29 May 2018 01:57:30 GMT

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

Re: Variables in specific order

Patrick — Tue, 29 May 2018 02:56:56 GMT

@@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&docsetVersion=9.4&locale=en

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;