topic Re: PROC Export to Excel, by variable/category, multiple databases in SAS Programming

PROC Export to Excel, by variable/category, multiple databases

colabear — Fri, 18 Jan 2019 21:11:32 GMT

I am trying to create separate reports by a category...in this case "state". There are four tables which I contain data which I need to populate into 4 sheets per state.

%macro a (StateNum, State);
ods tagsets.excelxp file="&qtr._&StateNum._&StateNum..xls"

path='H:\path_name'

style=statistical
options(sheet_interval='proc'
absolute_column_width="10,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9");

ods tagsets.excelxp options(sheet_name = "buildings");

proc print data=buildingfinal noobs label;
where StateNum=&StateNum.;
run;*/

ods tagsets.Excelxp options(sheet_name="streets");

proc print data=streets noobs label;

where StateNum=&StateNum.;
run;*/

ods tagsets.excelxp close;
%mend a;

%a(1, Alaska) ;
run; quit;

ods tagsets.excelxp close;

ods listing;

I am getting these errors:

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

ERROR 22-322: Syntax error, expecting one of the following: ;, (, ANCHOR, ARCHIVE, ATTRIBUTES,
AUTHOR, BASE, BODY, BOX_SIZING, CHARSET, CLOSE, CODE, CODEBASE, COLUMNS, CONTENTS,
CSSSTYLE, DATA, DOM, DPI, ENCODING, EVENT, FILE, FRAME, GFOOTNOTE, GPATH, GTITLE,
HEADTEXT, IMAGE_DPI, METATEXT, NEWFILE, NOGFOOTNOTE, NOGTITLE, OPERATOR, OPTIONS,
PACKAGE, PAGE, PARAMETERS, PATH, RECORD_SEPARATOR, SGE, STARTPAGE, STYLE,
STYLESHEET, TAGSET, TEXT, TRANTAB, TYPE, UNIFORM.
ERROR 202-322: The option or parameter is not recognized and will be ignored.

Any advise?

Re: PROC Export to Excel, by variable/category, multiple databases

Reeza — Fri, 18 Jan 2019 21:45:43 GMT

Your code has some comments symbols in random places and two ODS CLOSE.

Try cleaning some of those up.

Additionally, please run the code with:

options mprint symbolgen;

Re-run your macro to test it and post the log.

@colabear wrote:

I am trying to create separate reports by a category...in this case "state". There are four tables which I contain data which I need to populate into 4 sheets per state.

%macro a (StateNum, State);
ods tagsets.excelxp file="&qtr._&StateNum._&StateNum..xls"

path='H:\path_name'

style=statistical
options(sheet_interval='proc'
absolute_column_width="10,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9");

ods tagsets.excelxp options(sheet_name = "buildings");

proc print data=buildingfinal noobs label;
where StateNum=&StateNum.;
run;*/

ods tagsets.Excelxp options(sheet_name="streets");

proc print data=streets noobs label;

where StateNum=&StateNum.;
run;*/

ods tagsets.excelxp close;
%mend a;

%a(1, Alaska) ;
run; quit;

ods tagsets.excelxp close;

ods listing;

I am getting these errors:

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

ERROR 22-322: Syntax error, expecting one of the following: ;, (, ANCHOR, ARCHIVE, ATTRIBUTES,
AUTHOR, BASE, BODY, BOX_SIZING, CHARSET, CLOSE, CODE, CODEBASE, COLUMNS, CONTENTS,
CSSSTYLE, DATA, DOM, DPI, ENCODING, EVENT, FILE, FRAME, GFOOTNOTE, GPATH, GTITLE,
HEADTEXT, IMAGE_DPI, METATEXT, NEWFILE, NOGFOOTNOTE, NOGTITLE, OPERATOR, OPTIONS,
PACKAGE, PAGE, PARAMETERS, PATH, RECORD_SEPARATOR, SGE, STARTPAGE, STYLE,
STYLESHEET, TAGSET, TEXT, TRANTAB, TYPE, UNIFORM.
ERROR 202-322: The option or parameter is not recognized and will be ignored.

Any advise?

Re: PROC Export to Excel, by variable/category, multiple databases

colabear — Fri, 18 Jan 2019 22:35:12 GMT

OK here is the cleaned up code which worked:

options mprint symbolgen;

%let qtr = "SFY 2018 Quarter 1";
%macro a (State, StateName);
ods tagsets.excelxp file='&qtr._&State._&StateName..xls'

path='H:\Data'

style=statistical
options(sheet_interval='proc'
absolute_column_width="10,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9");

ods tagsets.excelxp options(sheet_name = "buildings");

proc print data=buildingfinal noobs label;
where State=&State.;
run;

ods tagsets.Excelxp options(sheet_name="streets");

proc print data=streets noobs label;

where State=&State.;
run;

%mend a;

%a(1, Alaska) %a(3, California) ;
run;
quit;

ods tagsets.excelxp close;

ods listing;

This solution generated an spreadsheet with the sheets I needed from each dataset, but only for one of the states. Another problem I am having is the filename for the output does not reflect the format I want: &qtr._&State._&StateName.

Thank you for helping me. At least i'm getting some progress!

Re: PROC Export to Excel, by variable/category, multiple databases

Reeza — Fri, 18 Jan 2019 23:36:38 GMT

This solution generated an spreadsheet with the sheets I needed from each dataset, but only for one of the states. Another problem I am having is the filename for the output does not reflect the format I want: &qtr._&State._&StateName.

Thank you for helping me. At least i'm getting some progress!

You need double quotes around the file path. You only get one response because it overwrites the previous file when it runs the second time because it's the same name.

@colabear wrote:

OK here is the cleaned up code which worked:

options mprint symbolgen;

%let qtr = "SFY 2018 Quarter 1";
%macro a (State, StateName);
ods tagsets.excelxp file='&qtr._&State._&StateName..xls'

path='H:\Data'

style=statistical
options(sheet_interval='proc'
absolute_column_width="10,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9");

ods tagsets.excelxp options(sheet_name = "buildings");

proc print data=buildingfinal noobs label;
where State=&State.;
run;

ods tagsets.Excelxp options(sheet_name="streets");

proc print data=streets noobs label;

where State=&State.;
run;

%mend a;

%a(1, Alaska) %a(3, California) ;
run;
quit;

ods tagsets.excelxp close;

ods listing;

This solution generated an spreadsheet with the sheets I needed from each dataset, but only for one of the states. Another problem I am having is the filename for the output does not reflect the format I want: &qtr._&State._&StateName.

Thank you for helping me. At least i'm getting some progress!

Re: PROC Export to Excel, by variable/category, multiple databases

colabear — Tue, 22 Jan 2019 19:22:11 GMT

Thank you for that, Reeza! I did get all 35 workbooks I needed with the four sheets of data needed. However, I will have to format with ODS Excel because I am getting the "The file you are trying to open, 'file.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?". So I have to click yes for each file, which is a nuisance given the volume of files.

The second problem I am having is--with the exception of the first output--all 4 sheet names are including numbers which are not specified anywhere.

Re: PROC Export to Excel, by variable/category, multiple databases

Reeza — Tue, 22 Jan 2019 22:46:07 GMT

ODS TAGSETS excelxp generates XML files, that's why. You can switch to ODS EXCEL if you have the appropriate version. Or save the files with an XML extension but open them with Excel.

Try setting the sheet_label to be blank explicitly to get rid of the numbers. And make sure you're using the latest version of the tag sets, you sometimes need to update it.

Re: PROC Export to Excel, by variable/category, multiple databases

colabear — Wed, 23 Jan 2019 18:45:37 GMT

Thank you so much for your help! I now have a successful program.