SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

EXPORT order of columns

Reply
New Contributor
Posts: 3

EXPORT order of columns

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!
Super Contributor
Posts: 260

Re: EXPORT order of columns

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.
New Contributor
Posts: 3

Re: EXPORT order of columns

Thanks Olivier. I'll try that.
New Contributor
Posts: 3

Re: EXPORT order of columns

I'm not clear on the syntax for doing a query. could you clarify?
Super Contributor
Posts: 260

Re: EXPORT order of columns

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.
N/A
Posts: 0

Re: EXPORT order of columns

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 */
Ask a Question
Discussion stats
  • 5 replies
  • 272 views
  • 0 likes
  • 3 in conversation