BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RAVI2000
Lapis Lazuli | Level 10

I have my proc means output like below

S_RAVI_0-1622741261868.png

 

I want the final output data set to be like below

S_RAVI_1-1622741495265.png

 

But when I proc transpose

proc transpose data = fin out = fin_t(drop = _name_) prefix = col;
	by armcode segment_code;
    var N mean median;
    id armcode;
run;

It is giving me like below

S_RAVI_2-1622741680213.png

col1, col2, col3 are the armcodes. How do I get the n, mean, median as column headings?

I want the vertical n, mean, median to be horizontal for every armcode.

 

Please correct me. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
data final;
input segment_code armcode N mean median;
cards;
1 1 3 1.00 1.00
2 1 5 2.00 2.00
3 1 4 3.00 3.00
1 2 0 0.00 0.00
2 2 0 0.00 0.00
3 2 0 0.00 0.00
1 3 0 0.00 0.00
2 3 0 0.00 0.00
3 3 0 0.00 0.00
;
run;

proc report data=final;
    columns ("Assessment" segment_code) ("Dose Level" armcode,(n mean median));
    define segment_code/group "Segment Code";
    define armcode/across "Arm Code";
    define n/analysis sum;
    define mean/analysis sum;
    define median/analysis sum;
run;
--
Paige Miller

View solution in original post

12 REPLIES 12
PaigeMiller
Diamond | Level 26

Do you want a report that can be printed or e-mailed or used in a presentation? Or do you want a dataset?

 

If you want a report, then PROC REPORT will do this relatively easily, and if you provide a portion of your SAS data set as data step code (as explained here), I can provide actual code.

 

If you want a data set, then do not (I repeat in bold capital letters, DO NOT) transpose the data this way, this only makes working with the data harder in almost every situation. Further explanation about what you are doing and what the end goal is would be needed.

--
Paige Miller
RAVI2000
Lapis Lazuli | Level 10
data final;
input segment_code 2. armcode 2. N $2. mean $4. median $4.;
;
cards;
1 1 3 1.00 1.00
2 1 5 2.00 2.00
3 1 4 3.00 3.00
1 2 0 0.00 0.00
2 2 0 0.00 0.00
3 2 0 0.00 0.00
1 3 0 0.00 0.00
2 3 0 0.00 0.00
3 3 0 0.00 0.00
;
run;

I am looking for a rtf output file report. Please let me know if you need any additional information.
Reeza
Super User
I'm suggesting you back up further. How did you create this data set, show that code? PROC TABULATE will generate it automatically in that format but PROC REPORT would be used to display summarized information.
PaigeMiller
Diamond | Level 26
data final;
input segment_code armcode N mean median;
cards;
1 1 3 1.00 1.00
2 1 5 2.00 2.00
3 1 4 3.00 3.00
1 2 0 0.00 0.00
2 2 0 0.00 0.00
3 2 0 0.00 0.00
1 3 0 0.00 0.00
2 3 0 0.00 0.00
3 3 0 0.00 0.00
;
run;

proc report data=final;
    columns ("Assessment" segment_code) ("Dose Level" armcode,(n mean median));
    define segment_code/group "Segment Code";
    define armcode/across "Arm Code";
    define n/analysis sum;
    define mean/analysis sum;
    define median/analysis sum;
run;
--
Paige Miller
RAVI2000
Lapis Lazuli | Level 10
Since my N, mean, median are character I am getting the below error.
ERROR: n is an ANALYSIS variable but not numeric.
ERROR: mean is an ANALYSIS variable but not numeric.
ERROR: median is an ANALYSIS variable but not numeric.
RAVI2000
Lapis Lazuli | Level 10
I had missing values in my data so I converted the N, MEAN, MEDIAN into character and assigned as "0", "0.00", "0.00".
Reeza
Super User
Change the word ANALYSIS to DISPLAY.
RAVI2000
Lapis Lazuli | Level 10

430 proc report data = fin;
431 columns ("Assessment" segment_code) ("Dose Level" armcode,(N mean
431 ! median));
432 define segment_code/group "Segment Code";
433 define armcode/across "Arm Code";
434 define N/display;
435 define mean/display;
436 define median/display;
437 run;

ERROR: There is no statistic associated with N.

ballardw
Super User

@RAVI2000 wrote:
I had missing values in my data so I converted the N, MEAN, MEDIAN into character and assigned as "0", "0.00", "0.00".

To quote Bill the Cat: "Gaaack!".

 

FORMATS are the way too control appearance in SAS output, not creating new variables.

 

Personally showing 0.00 for either a mean or median is a lie. You are claiming that the mean or median is 0 when in fact it does not exist.

0 for N is fine as that is an accurate count.

 

Proc format;

value misszero

. = '0'

;

run;

and use the MISSZERO. format for the numeric variable in the report display code. The default shown will use a BEST type format for any other value. Or you could explicitly set a different format for other values such as

Proc format;

value misszero

. = '0'

other= [Comma6.]

;

which would display non-missing values with the comma6. format.

 

A different format for the mean and/or median could show missing as something like 'N/A' for not available if you must have something.

RAVI2000
Lapis Lazuli | Level 10
I quite accept you!
PaigeMiller
Diamond | Level 26

@RAVI2000 wrote:
Since my N, mean, median are character I am getting the below error.
ERROR: n is an ANALYSIS variable but not numeric.
ERROR: mean is an ANALYSIS variable but not numeric.
ERROR: median is an ANALYSIS variable but not numeric.

Always ALWAYS ALWAYS a mistake to make numbers such as mean, median and n into character variables. Don't make your coding more difficult by doing things like this. As stated by @ballardw , formats control the appearance.

--
Paige Miller
Reeza
Super User
I think you should be using PROC TABULATE entirely here instead from scratch or look at the different output methods of PROC MEANS.

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
  • 12 replies
  • 966 views
  • 5 likes
  • 4 in conversation