topic Re: Splitting an observation into multiple lines with headings into Excel spreadsheet in ODS and Base Reporting

Splitting an observation into multiple lines with headings into Excel spreadsheet

tuncay — Tue, 12 Nov 2013 20:34:59 GMT

Hi everyone,

I have been looking for a way to output observations into two-three lines into an excel spreadsheet.

My dataset has about 20 columns. What I would like to achieve is output every observation into an excel spreadsheet (in portrait mode) in a couple of lines, with their headings. Business area wants to print all the details in the portrait mode to send the clients.

Variable1 Variable2 Variable3 Variable4

1 2 3 4

Variable5 Variable6 Variable7 . . .

5 6 7

Variable1 Variable2 ...

Any help will be much appreciated.

Thanks in advance.

Fatih

Re: Splitting an observation into multiple lines with headings into Excel spreadsheet

Reeza — Tue, 12 Nov 2013 20:46:40 GMT

You can probably do it using DDE and put statements.

I'm not sure if you can using ODS Tagsets, but imagine you can.

Re: Splitting an observation into multiple lines with headings into Excel spreadsheet

tuncay — Tue, 12 Nov 2013 21:39:05 GMT

Thanks for your reply Reeza.

Unfortunately we don't have DDE here. As for doing that with PUT statement, can you please elaborate a bit more?

Thanks

Fatih

Re: Splitting an observation into multiple lines with headings into Excel spreadsheet

Cynthia_sas — Wed, 13 Nov 2013 00:57:15 GMT

Hi:

Instead of using DDE, I would split up the data with a DATA step program and then use ODS and PROC REPORT to create the output. The program below takes the first 5 obs from SASHELP.SHOES and restructures the data by making 4 character variables (Var1, Var2, Var3 and Var4) and then making OBSNO and ORDNO variables that are used to maintain the order of the original observations and to make sure that row 1 of the obs comes before row 2 of the obs. And, also ORDNO is used to right justify the numeric variables (because otherwise, they would be left justified in the data cell).

cynthia

** make some fake data;
** for every obs in sashelp.shoes;
** create 2 obs in the diff_structure dataset;
** only have 4 vars in new dataset. All vars will be character.;

data diff_structure(keep=obsno ordno var1 var2 var3 var4);
length var1 var2 var3 var4 $40;
set sashelp.shoes(obs=5);
obsno = _n_;
ordno = 1;
var1 = region;
var2 = subsidiary;
var3 = product;
var4 = put(stores,comma6.);
output;
ordno = 2;
var1 = put(sales,dollar15.);
var2 = put(returns,dollar15.);
var3 = put(inventory,dollar15.);
var4 = ' ';
output;
run;

ods tagsets.excelxp file='c:\temp\two_rows_per_obs.xml'
style=sasweb
options(embedded_titles='yes' doc='Help');

proc report data=diff_structure nowd
style(column)={cellwidth=1.5in};
title '2 rows per obs';
column obsno ordno var1 var2 var3 var4;
define obsno / order 'Obs';
define ordno / order noprint;
define var1 / display 'Region/Sales';
define var2 / display 'Subsidiary/Returns';
define var3 / display 'Product/Inventory';
define var4 / display 'Number of Stores / ';
compute var4;
    ** right justify numeric variable(s) in each row;
    if ordno = 1 then do;
       call define(_col_,'style','style={just=r}');
    end;
    else if ordno = 2 then do;
       call define('var1','style','style={just=r}');
       call define('var2','style','style={just=r}');
       call define('var3','style','style={just=r}');
    end;
endcomp;
compute after obsno;
    **skip a line between every obs;
    line ' ';
endcomp;
run;

ods tagsets.excelxp close;

Re: Splitting an observation into multiple lines with headings into Excel spreadsheet

tuncay — Wed, 13 Nov 2013 01:41:57 GMT

Thank you so much Cynthia. So close to resolving my problem now.

Can I ask for one more favour ? Is there a possibility that I can write the column headings in different cells?

For example, in your attached file, Region and Sales variables are in the same cell (B3). Can we have Region in B3 and Sales in B5 then Region again in B7 (same for other variable names too) ?

Cheers

Fatih

Re: Splitting an observation into multiple lines with headings into Excel spreadsheet

Cynthia_sas — Wed, 13 Nov 2013 04:48:29 GMT

Hi:

I guess I don't understand what you want. If the column heading for 'Sales' moves to B5, where does the actual Sales number move to B6? Basically, it sounds like you want every Observation to get separate headers? I suppose it is possible with more data manipulation (not in PROC REPORT), but in the DATA step program that precedes it. I guess that means instead of 2 rows per observation, you would actually create 4 rows per observation -- rows 1 and 3 would be the header rows and rows 2 and 4 would be the observation rows? That sounds like it will be a very "busy" output file.

cynthia