topic Re: ODS output with no empty row at top where title would be and also volume count needed in ODS and Base Reporting

ODS output with no empty row at top where title would be and also volume count needed

Elliott — Fri, 01 Jul 2022 22:31:35 GMT

Hi, I am working on a report and I am using proc transpose and outputting into excel with ods.

The user wants there to be no title row the first row needs to be the column headings, also the user has requested the volume count to be to the right of the proc transpose output.

I need it to look like this and I don't know how to get the empty row at the top to not be there and the volume count to the right.

proc transpose data=sample_pop out=new(drop=_name_ _label_) prefix=acct;

by id;

var acctno;

run;

need output to look like this:

ID	acct1	acct2	acct3	acct4	Volume
1234567A	987456	546987			10
2564985D	123885	516795	158945	582185
5648595F	154879	852159	357951
8649254K	536598

Is this possible when creating output with ODS?

Thanks in advance,

Elliott

Re: ODS output with no empty row at top where title would be and also volume count needed

PaigeMiller — Sat, 02 Jul 2022 10:18:48 GMT

I am working on a report and I am using proc transpose and outputting into excel with ods. The user wants there to be no title ...

also the user has requested the volume count to be to the right

Since you have not shown us the ODS EXCEL part of your code, and you have not provided data to help us see where VOLUME is located; we're just guessing here. DO NOT MAKE US GUESS. Give us the information we need. Nevertheless, perhaps this works at getting rid of the title:

title;

As far as volume being on the right, you control this in PROC PRINT or PROC REPORT, not in PROC TRANSPOSE.

Re: ODS output with no empty row at top where title would be and also volume count needed

Elliott — Sat, 02 Jul 2022 18:39:46 GMT

Hi Paige,

Sorry I left out that information.

I have figured out how to suppress the title line.

So my next question is the volume count on the right most column.

I have figured out how to get the column where I want it using proc report and compute, but the value populates in every row and I only want it in the top most row.

Here is my code:

proc sort data=sample_pop; by id acctno; run;

proc sql noprint;

select count(*) INTO :obsvar

from SAMPLE_POP;

quit;

proc transpose data= sample_pop out=new(drop=_name_ _label_) prefix= acctno;

by id;

var acctno;

run;

proc report data=new;

column id acctno: volume;

define id /style(Column)= [tagattr='format:text' ];

define acctno: /style(Column)= [tagattr='format:text' ];

define volume / computed "Volume" format=8.;

compute volume;

volume = &obsvar.;

endcomp;

run;

this is what my output looks like:

ID	acctno 1	acctno 2	acctno 3	acctno 4	Volume
1234567A	987456	546987			10
2564985D	123885	516795	158945	582185	10
5648595F	154879	852159	357951		10
8649254K	536598				10

I need it to look like this:

ID	acctno 1	acctno 2	acctno 3	acctno 4	Volume
1234567A	987456	546987			10
2564985D	123885	516795	158945	582185
5648595F	154879	852159	357951
8649254K	536598

I am not sure how to only put the volume count in the upper right most top row.

Thanks,

Elliott

Re: ODS output with no empty row at top where title would be and also volume count needed

PaigeMiller — Sat, 02 Jul 2022 19:29:16 GMT

This would have to be done in a DATA step; although if you go back to the creation of this data set it could possibly be done at that time as well.

data want;
    set have; 
    if _n_>1 then call missing(volume);
run;

Re: ODS output with no empty row at top where title would be and also volume count needed

Elliott — Sat, 02 Jul 2022 22:01:43 GMT

unfortunately this did not work, it just removed the value from all the cells.

Re: ODS output with no empty row at top where title would be and also volume count needed

PaigeMiller — Sun, 03 Jul 2022 10:29:12 GMT

Show us the data set you are working with, the code you used, and the output data.

Re: ODS output with no empty row at top where title would be and also volume count needed

Elliott — Sun, 03 Jul 2022 13:30:21 GMT

Hi Paige,

Here is all my code and examples of what the output looks like an what I need it to look like:

data sample_pop;

input id $ acctno;

datalines;

1234567A 987456

1234567A 546987

2564985D 123885

2564985D 516795

2564985D 158945

2564985D 582185

5648595F 154879

5648595F 852159

5648595F 357951

8649254K 536598

;

run;

proc sort data=sample_pop; by id acctno; run;

proc sql noprint;

select count(*) INTO :obsvar

from SAMPLE_POP;

quit;

proc transpose data= sample_pop out=new(drop=_name_ _label_) prefix= acctno;

by id;

var acctno;

run;

proc report data=new;

column id acctno: volume;

define id /style(Column)= [tagattr='format:text' ];

define acctno: /style(Column)= [tagattr='format:text' ];

define volume / computed "Volume" format=8.;

compute volume;

volume = &obsvar.;

endcomp;

run;

Looks like this - I only want the volume to show in the top row:

ID	acctno 1	acctno 2	acctno 3	acctno 4	Volume
1234567A	987456	546987			10
2564985D	123885	516795	158945	582185	10
5648595F	154879	852159	357951		10
8649254K	536598				10

need output to look like this:

ID	acctno 1	acctno 2	acctno 3	acctno 4	Volume
1234567A	987456	546987			10
2564985D	123885	516795	158945	582185
5648595F	154879	852159	357951
8649254K	536598

I tried your suggestion with the dataset New and it just removed the volume count from all rows.

Thanks,

Elliott

Re: ODS output with no empty row at top where title would be and also volume count needed

PaigeMiller — Sun, 03 Jul 2022 18:42:56 GMT

I tried your suggestion with the dataset New and it just removed the volume count from all rows.

You don't show my suggestion anywhere in your code. How can we help you debug what is happening if you don't show us what you are doing that didn't work?

Nevertheless, this seems to fix the problem

proc transpose data= sample_pop out=new(drop=_name_ _label_) prefix= acctno;                                                               
   by id;                                                             
   var acctno;
run;
data new;
	set new;
	if _n_=1 then volume=&obsvar;
run;
proc report data=new;
    column id acctno: volume;
    define id  /style(Column)= [tagattr='format:text' ];
    define acctno:  /style(Column)= [tagattr='format:text' ];
    define volume / "Volume" format=8.;
run;

Re: ODS output with no empty row at top where title would be and also volume count needed

Elliott — Sun, 03 Jul 2022 19:09:28 GMT

Thank you for all your help, this is what I needed.
My code was getting me close but I just did not know how to only have volume in that first cell.

Thanks Again!
Elliott