Directing output to an Excel File

[shridula95]

Hi all

I need to use the Proc SURVEYMEANS procedure, and create an output data set that would include variable names, N, weighted proportion, and standard error, etc. Then using the DATA step and PROC TRANSPOSE steps, I need to further process this output, use the PROC PRINT and an ODS statement to direct the PROC PRINT output to an Excel destination.  The table needs to look like the picture attached. 

Here is the code I am trying and I get the excel output but not this form of the table. Could someone let me know where I am going wrong?

ODS HTML FILE='TH2.XLS' STYLESHEET;
proc surveymeans data=new.combined2 nobs mean stderr;
stratum STRAT_P;
cluster PSU_P;
weight FinalWeight;
var SPD;
domain sex group Race SRVY_YR SRVY_YR*group SRVY_YR*SEX SRVY_YR*Race;
ods output domain=new.Stats;
run;
data new.combined3;
set new.combined2;
length Demographics $12;
Demographics=catx(' ',trimn(group), trimn(sex), trimn(Race));
run;
data new.combined4;
retain Demographics _NAME_ COL1 COL2 COL3 COL4 COL5 COL6;
set new.combined3;
drop Race group sex;
run;
proc print data=new.combined4 label noobs;
label COL1="2010"
COL2="2011"
COL3="2012"
COL4="2013"
COL5="2014"
COL6="2015"
_NAME_='number';
run;
proc transpose data=new.combined4;
out=new.final;
by sex group race;
var N Mean Stderr;
run;
proc contents data=new.combined4;
run;
ods html close;
proc printto;
run;
ods rtf close;

Thank you

[PaigeMiller]

Could someone let me know where I am going wrong?

You have not told us what is going wrong. So we can't tell you where you are going wrong. It would also help — in fact, it's probably mandatory in this case — if you provide the data set you want to wind up in that final report layout.

But honestly, you make a fundamental logic mistake. The mistake you are making is that the final data set has to be in the exact same layout as the table you want to produce. While that might be true if SAS had never invented PROC TABULATE or PROC REPORT, SAS has indeed invented both of those procedures, and you don't have to transpose and rename variables and manipulate the data set and on and on to get the table you want. The data set does not have to be in the same layout as the table you want to produce. PROC REPORT ought to do this easily for you (and probably PROC TABULATE will do it easily as well, but I'm not very familiar with it).

[andreas_lds]

If you want to create an excel-file, why are you using ods hmtl instead of ods excel? And I recommend wrapping only the procedure in ods statements, whose output you want in the file, in your case only proc print.