topic Re: Proc Report issues: how to prevent data from automatic ordering in SAS Programming

Proc Report issues: how to prevent data from automatic ordering

A_Kh — Sat, 04 Mar 2023 03:26:53 GMT

Dear Community,

While reporting a data in proc report my VISIT variable's order keeps changing all the time. As original data is not alphanumerically "in order", proc report automatically changes it as below:

Month 12

Month 16

Month 20

Month 24

Month 28

Month 30

Month 32

Month 36

Month 48

Month 8

Week 12

However, I need Week 12 and Month 8 on top of the table. I used to create numeric variable like ORDER in order to maintain the original order, but I'm curious about whether there is more efficient way of avoiding abovementioned issue, like any option that helps to keep the original order from the dataset in proc report output.

Thank you!

Re: Proc Report issues: how to prevent data from automatic ordering

Kurt_Bremser — Sat, 04 Mar 2023 06:32:39 GMT

Use a number as data, and a format to display your strings. The sequence of the ascending number will control the layout.

Re: Proc Report issues: how to prevent data from automatic ordering

Shaik_Adil — Sat, 04 Mar 2023 12:44:44 GMT

@A_Kh

Usually by default SAS prints the data in ascending order.

As per your information I understood the values you mentioned are belong to "VISIT" variable.

And you might have using similar code to execute.

Proc report data=datasetname;
columns VISIT;
/* Define Visit /display; */
run;

For assisting you, can you please share the code and inputs for below questions:

* Values seems like not getting generated from SAS, and those are strings if yes then are you typing those manually?

* These values are constant or having any dependency?

Kindly correct me if am wrong.

Re: Proc Report issues: how to prevent data from automatic ordering

ballardw — Sat, 04 Mar 2023 14:26:36 GMT

One, ALWAYS show your code. The options you pick may be the cause of your issue.

IF your data is in the order that you want it to display before the call to Proc Report then you may be able to address the problem with the ORDER= option on the DEFINE statement for that variable

define Visit / order=data;

From the documentation:

DATA

orders values according to their order in the input data set

However, if your visit variable is not the first column then the options used defining that variable can make the Visit variable appear differently. (See why I say show your code).

Also if your data source is from an external database the order may well not be preserved depending on source and how the connection is implemented.

Re: Proc Report issues: how to prevent data from automatic ordering

A_Kh — Sun, 05 Mar 2023 00:47:43 GMT

@ballardw,

Thank you for your feedback. Below if my code from Proc report and Data (simulated) that i'm working on. I was using define VISIT/ group statement. But order=data option is what I'm looking for. It really does the job the way I expected. Thank you!

@Kurt_Bremser, I like the approach you suggested, but the above way seems more efficient in my situation. I appreciate your time and support!
@Shaik_Adil, I'm providing the data and codes below as an answer to your question. Thank you for checking in with me!

Again, thank you all for spending time to read and answer my post!



data have;
	length Visit $25; 
	input VISIT $ _n _mean _std ;
	datalines;
	Week12 210 0.55 0.84
	Month8 150 0.05 0.73
	Month12 120 0.07 0.51
	Month16 135 0.33 0.65
	Month20 101 0.06 0.43
	Month24 256 0.35 0.71
	Month28 175 0.11 0.43
	;
run; 

ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing
	options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1');
	proc report data= have spanrows style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt}
								 			style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} out=report_scorechange;
		column visit _n _mean _std;
		define visit/ "VISIT" width=25 style(header)= {just=l} style(column)= {just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} group;
		define _n/ "n" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define _mean/ "Mean" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define _std/ "SD" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		compute before visit/ style = {indent=2 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt};
			Line @3 Text $50.;
			length text $50;
			if strip(visit) eq "Week12" then text= "Reference Timepoint";
			else text= "Change from Week 12";
		endcomp;
	run; 
ods tagsets.excelxp close;