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
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;
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.
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
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;
Show us the data set you are working with, the code you used, and the output data.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.