topic Re: Proc report in SAS Procedures

Proc report

srav2 — Tue, 19 Dec 2017 08:37:38 GMT

hi,

I was trying to apply format in the proc report. But the order is not applied to the output. Below is my code.Please help.

proc format;
value $NAME
"0"= "Age (yrs)"
"1" = "   Number"
"2" = "   Mean(SD)"
"3" = "   Median"
"4" = "   Min : Max"
"5" = "Age group (yrs), N (%)"
"6" = "   <50"
"7" = "   50-59"
"8" = "   60-64"
"9" = "   >=65"
"10" = "Gender, N (%)"
"11" = "   Female"
"12" = "   Male"
"13" = "   U"
"14" = "Race, N (%)"
"15" = "   Black"
"16" = "   Asian/Oriental"
"17" = "   Causasian"
"18" = "   Other";
run;

proc report data = dm nowd headline spacing = 10 split="~";
columns _NAME_ _0_3_MG_KG_IV _1_0_MG_KG_IV _2_0_MG_KG_IV _2_5_MG_KG_IV TOTAL;
define _NAME_ /display " " style(column) = [asis=on] order = formatted format = $NAME.;
define _0_3_MG_KG_IV/display "0.3 mg/kg~IV~(N=&cnt1)" center;
define _1_0_MG_KG_IV/display "1.0 mg/kg~IV~(N=&cnt2)" center;
define _2_0_MG_KG_IV/display "2.0 mg/kg~IV~(N=&cnt3)" center;
define _2_5_MG_KG_IV/display "2.5 mg/kg~IV~(N=&cnt4)" center;
define TOTAL/display "Total~IV~(N=&cnt5)" center;
run;

Thanks

Re: Proc report

RW9 — Tue, 19 Dec 2017 09:06:44 GMT

Order is not applied because you have put the numbers as character. You have two options, you can sort the data using proc sort with the option of treating character as number:

proc sort data=have out=want sortseq=linguistic(Numeric_Collation=ON);

And then set order=data in your proc report. Or preferably, make your _name _ variable numeric (and as that is a SAS created variable possible from a means or transpose or something, fix it before that), then your proc format would be:

proc format;
  value $NAME
    0= "Age (yrs)"
    1 = "   Number"
    2 = "   Mean(SD)"
...
run;

And it will sort correctly.

Re: Proc report

RW9 — Tue, 19 Dec 2017 09:06:45 GMT

Order is not applied because you have put the numbers as character. You have two options, you can sort the data using proc sort with the option of treating character as number:

proc sort data=have out=want sortseq=linguistic(Numeric_Collation=ON);

proc format;
  value $NAME
    0= "Age (yrs)"
    1 = "   Number"
    2 = "   Mean(SD)"
...
run;

And it will sort correctly.

Re: Proc report

srav2 — Tue, 19 Dec 2017 09:14:54 GMT

Thanks for your solution.

But im having a doubt.

which datset we need to take in proc sort data

Re: Proc report

srav2 — Tue, 19 Dec 2017 09:21:25 GMT

hi,

I'm getting the following error. Please help.

proc sort data = dm out=want;
780 sortseq=linguistic(Numeric_Collation=ON);
     ----
     180
ERROR 180-322: Statement is not valid or it is used out of proper order.

781 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: Proc report

srav2 — Tue, 19 Dec 2017 09:27:24 GMT

hi,

The output I'm getting is as below in which order of the observations is not according to the format. Please help.

0.3 mg/kg IV(N=3) 1.0 mg/kg IV(N=5) 2.0 mg/kg IV(N=5) 2.5 mg/kg IV(N=2) Total IV(N=15)

Age (yrs)
Number	3	5	5	2	15
Mean (SD)	49.0(11.8)	61.4( 8.2)	61.4( 6.7)	51.0( 7.1)	57.5( 9.4)
Median	46.0	59.0	63.0	51.0	58.0
Min : Max	39:62	52:74	53:68	46:56	39:74
Age group (yrs), N(%)
50-59		3( 60.0%)	2( 40.0%)	1( 50.0%)	6( 40.0%)
60-64	1( 33.3%)	1( 20.0%)	1( 20.0%)		3( 20.0%)
<50	2( 66.7%)			1( 50.0%)	3( 20.0%)
>=65		1( 20.0%)	2( 40.0%)		3( 20.0%)
Gender, N (%)
Female	1( 33.3%)	3( 60.0%)	4( 80.0%)	2( 100.0%)	10( 66.7%)
Male	2( 66.7%)	1( 20.0%)	1( 20.0%)		4( 26.7%)
U		1( 20.0%)			1( 6.7%)
Race, N (%)
ASIAN/ORIENTAL	1( 33.3%)		2( 40.0%)	1( 50.0%)	4( 26.7%)
BLACK		2( 40.0%)	3( 60.0%)		5( 33.3%)
CAUCASIAN	2( 66.7%)	3( 60.0%)			5( 33.3%)
OTHER				1( 50.0%)	1( 6.7%)

Re: Proc report

RW9 — Tue, 19 Dec 2017 09:35:23 GMT

As I said, I recommend not doing it this way. However:

proc sort data=dm sortseq=linguistic(Numeric_Collation=ON);
  by _name_;
run;
proc report data = dm nowd headline spacing = 10 split="~";
  columns _NAME_ _0_3_MG_KG_IV _1_0_MG_KG_IV _2_0_MG_KG_IV _2_5_MG_KG_IV TOTAL;
  define _NAME_ /display " " style(column) = [asis=on] order = data format = $NAME.;
  define _0_3_MG_KG_IV/display "0.3 mg/kg~IV~(N=&cnt1)" center;
  define _1_0_MG_KG_IV/display "1.0 mg/kg~IV~(N=&cnt2)" center;
  define _2_0_MG_KG_IV/display "2.0 mg/kg~IV~(N=&cnt3)" center;
  define _2_5_MG_KG_IV/display "2.5 mg/kg~IV~(N=&cnt4)" center;
  define TOTAL/display "Total~IV~(N=&cnt5)" center;
run;

Re: Proc report

srav2 — Tue, 19 Dec 2017 10:20:37 GMT

Hi,

The output is displayed in this manner which is not present in format order.

<50	2( 66.7%)			1( 50.0%)	3( 20.0%)
>=65		1( 20.0%)	2( 40.0%)		3( 20.0%)
50-59		3( 60.0%)	2( 40.0%)	1( 50.0%)	6( 40.0%)
60-64	1( 33.3%)	1( 20.0%)	1( 20.0%)		3( 20.0%)
Age (yrs)
Age group (yrs), N(%)
ASIAN/ORIENTAL	1( 33.3%)		2( 40.0%)	1( 50.0%)	4( 26.7%)
BLACK		2( 40.0%)	3( 60.0%)		5( 33.3%)
CAUCASIAN	2( 66.7%)	3( 60.0%)			5( 33.3%)
Female	1( 33.3%)	3( 60.0%)	4( 80.0%)	2( 100.0%)	10( 66.7%)
Gender, N (%)
Male	2( 66.7%)	1( 20.0%)	1( 20.0%)		4( 26.7%)
Mean (SD)	49.0(11.8)	61.4( 8.2)	61.4( 6.7)	51.0( 7.1)	57.5( 9.4)
Median	46.0	59.0	63.0	51.0	58.0
Min : Max	39:62	52:74	53:68	46:56	39:74
Number	3	5	5	2	15
OTHER				1( 50.0%)	1( 6.7%)
Race, N (%)
U		1( 20.0%)			1( 6.7%)

Re: Proc report

RW9 — Tue, 19 Dec 2017 10:24:40 GMT

Provide some test data in the form of a datastep. And show what code you are running. I can't guess what the problem is.

Re: Proc report

srav2 — Tue, 19 Dec 2017 10:35:20 GMT

hi,

I'm having the data like the table. I want to change the observations order of first column.

I need the order like below.

Age (yrs)

Number

Mean (SD)

Median

Min : Max

Age group (yrs), N (%)

<50

50-59

60-64

>=65

Gender, N (%)

Female

Male

Race, N (%)

Black

Asian/Oriental

Caucasian

Other

0.3 mg/kg IV(N=3) 1.0 mg/kg IV(N=5) 2.0 mg/kg IV(N=5) 2.5 mg/kg IV(N=2) Total IV(N=15)

Age (yrs)
Number	3	5	5	2	15
Mean (SD)	49.0(11.8)	61.4( 8.2)	61.4( 6.7)	51.0( 7.1)	57.5( 9.4)
Median	46.0	59.0	63.0	51.0	58.0
Min : Max	39:62	52:74	53:68	46:56	39:74
Age group (yrs), N(%)
50-59		3( 60.0%)	2( 40.0%)	1( 50.0%)	6( 40.0%)
60-64	1( 33.3%)	1( 20.0%)	1( 20.0%)		3( 20.0%)
<50	2( 66.7%)			1( 50.0%)	3( 20.0%)
>=65		1( 20.0%)	2( 40.0%)		3( 20.0%)
Gender, N (%)
Female	1( 33.3%)	3( 60.0%)	4( 80.0%)	2( 100.0%)	10( 66.7%)
Male	2( 66.7%)	1( 20.0%)	1( 20.0%)		4( 26.7%)
U		1( 20.0%)			1( 6.7%)
Race, N (%)
ASIAN/ORIENTAL	1( 33.3%)		2( 40.0%)	1( 50.0%)	4( 26.7%)
BLACK		2( 40.0%)	3( 60.0%)		5( 33.3%)
CAUCASIAN	2( 66.7%)	3( 60.0%)			5( 33.3%)
OTHER				1( 50.0%)	1( 6.7%)

PROC Report

srav2 — Tue, 19 Dec 2017 15:29:51 GMT

Hi,

Can anyone please explain how to change the order of observations in proc report.

I tried creating format and applied in proc report. But it is not working.

I need the _NAME_ variable to be displayed in the order that is mentioned in the format.

Thanks

Re: PROC Report [how to improve your question]

Community_Guide — Tue, 19 Dec 2017 15:23:32 GMT

Hello @srav2,

Your question requires more details before experts can help. Can you revise your question to include more information?

Review this checklist:

Specify a meaningful subject line for your topic. Avoid generic subjects like "need help," "SAS query," or "urgent."
When appropriate, provide sample data in text or DATA step format. See this article for one method you can use.
If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message. From there you can adjust the title and add more details to the body of the message. Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @ballardw

Re: Proc report

ballardw — Tue, 19 Dec 2017 16:13:38 GMT

@srav2 wrote:

Hi,

The output is displayed in this manner which is not present in format order.

<50 2( 66.7%) 1( 50.0%) 3( 20.0%)

>=65 1( 20.0%) 2( 40.0%) 3( 20.0%)

50-59 3( 60.0%) 2( 40.0%) 1( 50.0%) 6( 40.0%)

60-64 1( 33.3%) 1( 20.0%) 1( 20.0%) 3( 20.0%)

Age (yrs)

Age group (yrs), N(%)

ASIAN/ORIENTAL 1( 33.3%) 2( 40.0%) 1( 50.0%) 4( 26.7%)

BLACK 2( 40.0%) 3( 60.0%) 5( 33.3%)

CAUCASIAN 2( 66.7%) 3( 60.0%) 5( 33.3%)

Female 1( 33.3%) 3( 60.0%) 4( 80.0%) 2( 100.0%) 10( 66.7%)

Gender, N (%)

Male 2( 66.7%) 1( 20.0%) 1( 20.0%) 4( 26.7%)

Mean (SD) 49.0(11.8) 61.4( 8.2) 61.4( 6.7) 51.0( 7.1) 57.5( 9.4)

Median 46.0 59.0 63.0 51.0 58.0

Min : Max 39:62 52:74 53:68 46:56 39:74

Number 3 5 5 2 15

OTHER 1( 50.0%) 1( 6.7%)

Race, N (%)

U 1( 20.0%) 1( 6.7%)

First thing to try in is in the Proc Format to code to add (notsorted) after the name of the format.

Better to use a numeric not character variable to order the values. If you use the internal order of a variable that is character then "11" comes before "2" because the first character "1" comes before "2" at that is far as the sort goes.

Or change your character order variables and the associated so that the order is correct "01" not "1" then "11" comes after "02" through "09" because the "0" comes before the first "1".

Re: PROC Report

ballardw — Tue, 19 Dec 2017 17:01:52 GMT

DATA. We need DATA to see what is going on.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: PROC Report

srav2 — Wed, 20 Dec 2017 05:40:56 GMT

hi,

The sample data is as below. In the final report i need to change the order of the observations in the variable race$.

data out. dm;
length region$ 15 ethnicity$ 25 Trtgrp$ 20;
input studyid$ age height weight BSA region$ sex$ race$ ethnicity$ Trtgrp$;
datalines;
001 50 112 60 2.3 NorthAmerica Men White Caucasian Trt1
002 66 123 62 2.1 NorthAmerica Women White AfricanAmerican Trt1
003 68 136 55 2.6 Europe Women Black Caucasian Trt1
004 70 128 69 1.9 Europe Men White Latino Trt1
005 78 130 74 1.6 NorthAmerica Men White Caucasian Trt1
006 76 124 66 1.8 Europe Women Black AfricanAmerican Trt2
007 77 126 64 2.4 Europe Men Black Caucasian Trt2
008 65 122 70 1.5 NorthAmerica Men White AfricanAmerican Trt1
009 67 132 73 1.3 Europe Men White Caucasian Trt1
010 69 128 76 1.4 NorthAmerica Women Black Caucasian Trt2
;
run;

proc report data = out.Demographics nowd headline spacing = 10;
columns _NAME_ trt1 trt2 total;
define _NAME_ /display " " center;
define trt1 /display "TRT1" center;
define trt2 /display "TRT2" center;
define total/display "Total" center;
run;

Re: PROC Report

RW9 — Wed, 20 Dec 2017 09:24:32 GMT

What you have posted is incorrect. There is a space before dm where there should be none, and your proc report does uses a different dataset, which has different variables. What I will do is once again, as you seem to refuse to provide simple requested information, is guess what you want. I will take your given dm, and order by north american first then Europe, this shows how to create an order variable, and use that in your proc report. Its the nearest I can get to with limited information.

data dm;
  length region$ 15 ethnicity$ 25 Trtgrp$ 20;
  input studyid$ age height weight BSA region$ sex$ race$ ethnicity$ Trtgrp$;
datalines;
001 50 112 60 2.3 NorthAmerica Men White Caucasian Trt1
002 66 123 62 2.1 NorthAmerica Women White AfricanAmerican Trt1
003 68 136 55 2.6 Europe Women Black Caucasian Trt1
004 70 128 69 1.9 Europe Men White Latino Trt1
005 78 130 74 1.6 NorthAmerica Men White Caucasian Trt1
006 76 124 66 1.8 Europe Women Black AfricanAmerican Trt2
007 77 126 64 2.4 Europe Men Black Caucasian Trt2
008 65 122 70 1.5 NorthAmerica Men White AfricanAmerican Trt1
009 67 132 73 1.3 Europe Men White Caucasian Trt1
010 69 128 76 1.4 NorthAmerica Women Black Caucasian Trt2
;
run;

data dm;
  set dm;
  ord=ifn(race="NorthAmerica",1,2);
run;

proc report data=dm nowd headline spacing = 10;
  columns studyid trtgrp ord race age height weight;
  define ord / noprint;
run;

Re: PROC Report

ballardw — Thu, 21 Dec 2017 16:39:28 GMT

@srav2 wrote:

hi,

The sample data is as below. In the final report i need to change the order of the observations in the variable race$.

data out. dm;
length region$ 15 ethnicity$ 25 Trtgrp$ 20;
input studyid$ age height weight BSA region$ sex$ race$ ethnicity$ Trtgrp$;
datalines;
001 50 112 60 2.3 NorthAmerica Men White Caucasian Trt1
002 66 123 62 2.1 NorthAmerica Women White AfricanAmerican Trt1
003 68 136 55 2.6 Europe Women Black Caucasian Trt1
004 70 128 69 1.9 Europe Men White Latino Trt1
005 78 130 74 1.6 NorthAmerica Men White Caucasian Trt1
006 76 124 66 1.8 Europe Women Black AfricanAmerican Trt2
007 77 126 64 2.4 Europe Men Black Caucasian Trt2
008 65 122 70 1.5 NorthAmerica Men White AfricanAmerican Trt1
009 67 132 73 1.3 Europe Men White Caucasian Trt1
010 69 128 76 1.4 NorthAmerica Women Black Caucasian Trt2
;
run;

proc report data = out.Demographics nowd headline spacing = 10;
columns _NAME_ trt1 trt2 total;
define _NAME_ /display " " center;
define trt1 /display "TRT1" center;
define trt2 /display "TRT2" center;
define total/display "Total" center;
run;

Your proc report does not use the example data, does not use the variable race at all, uses a variable _name_ not in the example data, and you need to describe what order you want race to appear. You say "change the order of the observations in the variable race". To what?