BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Elliott
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Elliott
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Elliott
Obsidian | Level 7
unfortunately this did not work, it just removed the value from all the cells.
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Elliott
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
Elliott
Obsidian | Level 7
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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 585 views
  • 0 likes
  • 2 in conversation