topic Re: How i can do sorting from proc format by second column and not first like it does in SAS Programming

How i can do sorting from proc format by second column and not first like it does

alexmathp — Wed, 09 Dec 2020 13:19:19 GMT

Hi to all,

I want to ask how we can do sorting from proc format by second column and not first like it does by default.

To make it clear what i mean, i want to my formats to be sorted by second column (the red circle) of the below picture

Re: How i can do sorting from proc format by second column and not first like it does

PeterClemmensen — Wed, 09 Dec 2020 13:21:29 GMT

I dont understand this. You want help to restructure the code or?

Re: How i can do sorting from proc format by second column and not first like it does

PaigeMiller — Wed, 09 Dec 2020 13:22:29 GMT

Explain further. There is really no concept of sorting formats in SAS. Where should we see this proper sorting? In the output of some procedure? In a data set?

Re: How i can do sorting from proc format by second column and not first like it does

alexmathp — Wed, 09 Dec 2020 13:23:14 GMT

when i load the formats in my data the sorting is from first column and not second like i want

Re: How i can do sorting from proc format by second column and not first like it does

Kurt_Bremser — Wed, 09 Dec 2020 13:34:30 GMT

Absolutely irrelevant. When the format is applied, later procedure steps will either order by formatted or unformatted values anyway, depending on the procedure.

If you want nicely ordered code, shuffle your code lines by selecting and moving.

Re: How i can do sorting from proc format by second column and not first like it does

Tom — Wed, 09 Dec 2020 13:35:07 GMT

Reordering the lines of the code used to generate the format will not have any effect on how the format is defined.

What is it that you really want to do?

If you want something else to sort by the formatted values then store the formatted values into a another variable and sort by that.

If you are using PROC REPORT then use the ORDER=FORMATTED option. Note that is the default ordering for PROC REPORT. https://support.sas.com/resources/papers/proceedings11/090-2011.pdf

Re: How i can do sorting from proc format by second column and not first like it does

alexmathp — Wed, 09 Dec 2020 13:35:17 GMT

Yes i want to my data step to sort by the second column and not first like it does. Tell me if it clear now ok

Re: How i can do sorting from proc format by second column and not first like it does

alexmathp — Wed, 09 Dec 2020 13:44:00 GMT

I want to make fac and bid file from the proc formats that i did already and the sorting to be done from the second column in some cases. If it is applicable of course?

Re: How i can do sorting from proc format by second column and not first like it does

Kurt_Bremser — Wed, 09 Dec 2020 13:44:02 GMT

The DATA step does not sort on its own. Certain procedures will order their output automatically.

If you want PROC SORT to sort by formatted values, you have to create a new variable containing the formatted values first.

In SQL, you can do that "on the fly":

proc format;
value mytest
  1 = "B"
  2 = "A"
;

data have;
input value;
format value mytest.;
datalines;
1
2
;

proc sql;
create table want as
  select put(value,mytest.) as value
  from have
  order by calculated value
;
quit;

Re: How i can do sorting from proc format by second column and not first like it does

PaigeMiller — Wed, 09 Dec 2020 13:59:32 GMT

@alexmathp wrote:
Yes i want to my data step to sort by the second column and not first like it does. Tell me if it clear now ok

You have not answered my questions: "Where should we see this proper sorting? In the output of some procedure? In a data set?"

Re: How i can do sorting from proc format by second column and not first like it does

ballardw — Wed, 09 Dec 2020 15:46:17 GMT

Please provide code by posting text in a code box.

I was going to show some affects of Formats and Procedure options but since you did not provide the code for the format as code I could execute I can't. I'm too lazy to retype someone else's work.

The PROCEDURE you use to display values will depend somewhat as to what you need to do get formatted values in a specific order (or not).

And since you have the formatted values '09' '07' and '08' appear more than once in the format it is not very clear what expected order you would expect.