BookmarkSubscribeRSS Feed
jrushing
Calcite | Level 5
Is there a way to specify to proc export the order of columns to be output to EXCEL? I can't find any doc that says how to do this.

thanks!
9 REPLIES 9
Olivier
Pyrite | Level 9
Change the order of your columns in a query, the result of which you export to Excel.
No options in the Export procedure for that.
The other option is to use the Print procedure (specify the variable order in the VAR statement) and create Excel output from it with ODS HTML or TAGSETS.EXCELXP.
jrushing
Calcite | Level 5
Thanks Olivier. I'll try that.
jrushing
Calcite | Level 5
I'm not clear on the syntax for doing a query. could you clarify?
ballardw
Super User

Another is to use a different procedure than Export.

 

Dummy code:

ods excel file="path/yourfilename.xlsx";

proc print data=yourdatasetname noobs label;
   var firstvarname secondvarname thirdvarname;
run;

ods excel close;

One advantage of Proc Print is that you can use variable lists. If you have variables with names like measure1, measure2, ..., measure50 you could use Measure1-Measure50 on the var statement if you want them in numeric order. Or if some of variables are already together in order in the data set but you want to place them before or after other variables you use a list with two dashes : thisvar -- thatvar. This list uses those variables in the order they appear in the dataset. Of if you want all variables whose names start with XYZ together but may not specifically care about order within the group use XYZ:  the colon tells SAS to use all variable whose names start with XYZ. The order is usually alphabetic. SQL will not allow use of any of the lists constructs. So if you have many variables this may be quite a bit easier on the typing.

 

Additionally you can provide changes to variable label just for the print statement or style overrides such as cell colors based on values.

PaigeMiller
Diamond | Level 26

or use PROC TABULATE or PROC REPORT with ODS EXCEL, which would have many of the same benefits as described by @ballardw 

--
Paige Miller
Reeza
Super User
ODS EXCEL wasn't around in 2007. Old questions popping up for everyone is an interesting side effect of the new forum layout but does allow for people to point out newer usages..
Olivier
Pyrite | Level 9
By "query" I was meaning something like :

PROC SQL ;
CREATE TABLE yourData AS
SELECT firstVar, secondVar, ...
/* you need to write them all */
FROM originalData
;
QUIT ;

Dunno why, but I was thinking you were using Excel (stupid me). So as long as you are programming, you'd rather use the PROC PRINT solution, which will run much faster (does not need to create a copy of your dataset).

Another solution is to reorder variables with a data step, using a "dummy" label (or format, or informat) statement to name them in the order you want, BEFORE your SET statement. This will cause SAS to use your order instead of the original you. It can be useful if you only have a few variables to be moved to the very left of your dataset, for example if original order is T U V W X Y Z you can do :

DATA reordered ;
LABEL X = "The X label"
Z = "The Z label" ;
SET original ;
RUN ;

In the new dataset the order is X Z T U V W Y.
brian20435
Calcite | Level 5
Olivier, both replies you gave were EXTREMELY helpful. Thanks for taking the time to do so.
deleted_user
Not applicable
I don't have a solution using proc export but I did develop a set of macros that writes SAS values to any cell(s) of any worksheet of any workbook. This code wrote SAS data to 28 worksheets. The workbook must be open and the worksheets already named to the names in this macro for this to work. The first step clears all the data in the sheet. The second and third steps write column names. The fourth step writes all the data.

/* Write the Rank file */


libname pcdata "H:\sas\codelib\excellib";
filename cmds dde 'excel|system'; /* Excel reference */
%global strt stp cstrt cstp;

%let prod=Sheet; /* Sheet Name Prefix Constant */
%let rank=1; /* Sheet Name Suffix Variable */
%let strt=1; /* Row start number */
%let stp=10000; /* Row stop number */
%let cstrt=1; /* Column start number */
%let cstp=2; /* Column stop number */
%let hospdir=H:\sas\codelib\excellib\; /* path */
%let wkbk=Rankout.xls;

/* by number */

/* Set the workbook and */
/* worksheet macros */
/* write the file */

%macro namefil;
data _null_;
call symput ("opensht&rank.",
"'"||'EXCEL|'||"&hospdir"||"[&wkbk.]"||"∏&rank"||'!r'||
"&strt"||'c'||"&cstrt"||':r'||"&stp"||'C'||"&cstp"||"'");
run;

%mend namefil;

%macro checkit;
%put &&opensht&rank;
filename r&prod.&rank. dde &&opensht&rank. notab;
%mend checkit;

%macro writemac;
call symput('strt',trim(left("&strt.")));
call symput('stp', trim(left("&stp.")));
call symput('cstrt',trim(left("&cstrt.")));
call symput('cstp',trim(left("&cstp.")));
%mend writemac;

%macro datamac;
var1 = trim(left(varnam1));
var2 = trim(left(varnam2));
tab = '09'x;
file r&prod.&rank. dsd delimiter='09'x ;
put var1 var2 ;
%mend datamac;


%macro writefil;
%do first=1 %to 4;

%if &first = 1 %then %do;
/* empty the worksheet */
%let strt=1; /* Row start number */
%let stp=10000; /* Row stop number */
%let cstrt=1; /* Column start number */
%let cstp=2; /* Column stop number */
%namefil;
data _null_;
length stp 8;length spaces $ 8;
stp = left(trim("&stp"));
spaces = " ";
call symput('spaces',spaces);
tab = '09'x;
file r&prod.&rank. dsd delimiter='09'x;
do i=1 to stp;
put spaces spaces;
end;
run;
%end;

%if &first=2 %then %do;
/* create the macros for datamac */
data _null_;
%let strt=1;
%let stp=1;
%let cstrt=1;
%let cstp=2;
%writemac
run;

data _null_;
call execute('%namefil');
run;

data _null_;
%checkit;
run;

/* write the rank to first row first column */
data temp;
set finalX;
*set excelfil;
where rank = &rank;
run;

data _null_;
length varnam1 varnam2 $10;
set temp(obs=1);
varnam1 = "Rank = "||TRIM(LEFT(put(rank,8.)));
varnam2 = FMTNAME;
%datamac;
run;
%end;

/* write the second row first column */

%if &first = 3 %then %do;
/* write the rank to second row first column */
data _null_;
%let strt=2;
%let stp=2;
%let cstrt=1;
%let cstp=2;
%writemac
run;

data _null_;
call execute('%namefil');
run;

data _null_;
%checkit;
run;

data _null_;
length varnam1 $35 varnam2 $8;
set temp(obs=1);
varnam1 = category;
varnam2 = "&SPACES";
%datamac;
run;
%end;


/* write the third row in first and second columns */

%if &first = 4 %then %do;
data _null_;
%let strt=3;
%let stp=10000;
%let cstrt=1;
%let cstp=2;
%writemac
run;

data _null_;
call execute('%namefil');
run;

data _null_;
%checkit;
run;
/* populate the sheet */

data _null_;
length varnam1 varnam2 $8;
set temp(keep=start end fmtname rank);
varnam1 = start;
varnam2 = end;
if rank = 99 then do;
if varnam2 = ' ' then varnam2 = fmtname;
end;
file r&prod.&rank. dsd delimiter='09'x;
put varnam1 varnam2
;
run;
%end;
%end;
%mend writefil;

options mlogic nosymbolgen mprint nomacrogen;
%let rank=1; /* Sheet Name Suffix Variable */
%writefil;

%MACRO TEST;
%DO I=1 %TO 28;
%LET RANK=&I;
%WRITEFIL;
%END;
%MEND TEST;
%TEST;

%let rank=98; /* Sheet Name Suffix Variable */
%writefil;
%let rank=99; /* Sheet Name Suffix Variable */
%writefil;


/* rebuild the worksheet each time you read or write to it */
/* Rankings match the sheet numbers */

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!

Discussion stats
  • 9 replies
  • 3728 views
  • 2 likes
  • 7 in conversation