topic Re: Transpose the n, mean median along with dosage. in SAS Programming

Transpose the n, mean median along with dosage.

RAVI2000 — Thu, 03 Jun 2021 17:38:17 GMT

I have my proc means output like below

I want the final output data set to be like below

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

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!

Re: Transpose the n, mean median along with dosage.

PaigeMiller — Thu, 03 Jun 2021 17:54:37 GMT

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.

Re: Transpose the n, mean median along with dosage.

Reeza — Thu, 03 Jun 2021 18:06:02 GMT

I think you should be using PROC TABULATE entirely here instead from scratch or look at the different output methods of PROC MEANS.

Re: Transpose the n, mean median along with dosage.

RAVI2000 — Thu, 03 Jun 2021 19:12:49 GMT

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.

Re: Transpose the n, mean median along with dosage.

Reeza — Thu, 03 Jun 2021 19:20:44 GMT

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.

Re: Transpose the n, mean median along with dosage.

PaigeMiller — Thu, 03 Jun 2021 19:26:31 GMT

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;

Re: Transpose the n, mean median along with dosage.

RAVI2000 — Thu, 03 Jun 2021 20:17:22 GMT

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.

Re: Transpose the n, mean median along with dosage.

RAVI2000 — Thu, 03 Jun 2021 20:19:55 GMT

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

Re: Transpose the n, mean median along with dosage.

Reeza — Thu, 03 Jun 2021 20:22:30 GMT

Change the word ANALYSIS to DISPLAY.

Re: Transpose the n, mean median along with dosage.

RAVI2000 — Thu, 03 Jun 2021 20:28:32 GMT

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.

Re: Transpose the n, mean median along with dosage.

ballardw — Thu, 03 Jun 2021 20:41:52 GMT

@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.

Re: Transpose the n, mean median along with dosage.

RAVI2000 — Thu, 03 Jun 2021 20:53:54 GMT

I quite accept you!

Re: Transpose the n, mean median along with dosage.

PaigeMiller — Thu, 03 Jun 2021 21:04:36 GMT

@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.