topic Re: Changing EXCEL output in ODS and Base Reporting

Changing EXCEL output

DanielQuay — Mon, 04 Apr 2022 13:47:13 GMT

Hello All,

So I have a lot of variables that I am needing to print out using a BY variable.

The problem is that when it goes to the Excel file, I wind up with a single row that is going from A to EQ.

I'd rather have this print as columns or like, A - G then go down a row and repeat.

I've been trying to research this but I haven't found the solution yet.

Here is my code (abbreviated):

ods excel file="H:\PAMR Program\SAS Codes\Certificate Data\Birth Certificate Data_&sysdate..xlsx" ;
proc print data = PAMR_CERT noobs;
var nchsap5 nchsap10 livedep brefed ctrans ctranslit forcep
vacuum fetpres delroute trial vent surf seiz ventl6 nicuadm newbanti bthinj nchs_abcond
anenc cyanotic omph limbred cleflip downsyn hypspad spina hern gastro clefpal suspchrom
cgaoth nchs_cgana ;
by patnm;
format CDOB mmddyy10. fdob mmddyy10. mdob mmddyy10. lldob mmddyy10. lodob monyy7. nchsbpt $nchsbpt. Attend_Type $Attend_Type.
MOM_XFER $MOM_XFER. vrfac $Fac2020a. nchsfedu $educ. marrever $marrever. pataff $pataff. nchsmedu $educ.;
run;

Am I wanting to do the impossible here?

Re: Changing EXCEL output

Tom — Mon, 04 Apr 2022 14:32:29 GMT

I don't understand. You printed 33 variables. So you get 33 columns in the spreadsheet.

What it is that you want instead?

Re: Changing EXCEL output

DanielQuay — Mon, 04 Apr 2022 14:35:57 GMT

The problem is that I need this to basically show up on one page.

So instead of one row with 33 variables I would need like 8 rows with 4 variables and one row of 1.

Re: Changing EXCEL output

Tom — Mon, 04 Apr 2022 14:41:47 GMT

If you want 8 rows then re-organize the dataset to reflect the new structure.

It would help a lot if you posted some example input data and what you want the output to look like.

But remember that SAS is dealing with datasets, not cells in a spreadsheet. So if you can design a dataset structure that reflects what you want then PROC PRINT or PROC REPORT will work.

But if you want very fine grained support for report generating then look at the tools for generating ODS output from a data step.

Re: Changing EXCEL output

ghosh — Wed, 06 Apr 2022 15:01:57 GMT

One approach would be to read your data into a matrix using proc iml. then you can reshape it into the dimension you want for your Excel sheet.

Below is a rough code to display 32 vars into a 4x8 table. if you need to add another row say with just one var, you could append another matrix using the "//" operator (vertical concatenation).

Hope this helps

/* Create a dummy dataset that represents your PAMR_CERT */
proc iml;
  mydat=(1:32);
  create PAMR_CERT from mydat;
    append from mydat;
  close;
quit;

/* Your code starts here */
/* reshape the data into 4 x 8  */
proc iml;
  use PAMR_CERT; /* Here use your actual dataset   */
  read all var _NUM_ into x[colname=NumNames];
  print x;
  close PAMR_CERT;
  z=shape(x,8,4);  
  print z;
  create PAMR_CERT2 from z[colname={'A' 'B' 'C' 'D'}];	
	append from z;
  close;
quit;
filename xlout '~/test/reshaped.xlsx';
ods excel file=xlout;
proc print;
run;
ods excel close;

Re: Changing EXCEL output

andreas_lds — Tue, 19 Apr 2022 13:50:29 GMT

Have a look at the report writing interface (RWI). It is used in a dataset and allows nearly full control over the output format.