topic Re: Proc SQL order by value not ascii in New SAS User

Proc SQL order by value not ascii

cactooos — Thu, 19 Mar 2020 11:36:45 GMT

Hi,

Just wondering if there is any better way of having kind of custom group order that the code below. I have format:

proc format ;
value valband
0 = '0'
0<-30 = '0-30'
30<-90 = '30-90'
90<-215 = '90-215'
215<-430= '215-430'
430-high = 'over 430';
run ;

But when I do group by in proc sql I see the order:

0
0-30
215-430
30-90 etc.

obviously I would like to have it ordered by value. I found a solution with additional ranking created by case when, is this the only/easiest way of doing this?

order by case when val_bin = '0' then 1
	when val_bin = '0-30' 		then 2
	when val_bin = '30-90' 		then 3
	when val_bin = '90-215' 	then 4
	when val_bin = '215-430' 	then 5
	when val_bin = 'over 430' 	then 6 
end

Re: Proc SQL order by value not ascii

PaigeMiller — Thu, 19 Mar 2020 11:41:15 GMT

If you are using PROC SQL to create data sets, it really doesn't matter what order the data is in. You can always use PROC TABULATE or PROC REPORT to obtain an output in the order you want. Please make note of this concept: your SAS data set does not have to be in the order you want it when a print or output is desired; the output procedure can do the ordering for you.

If you are using PROC SQL to create a report, then you need a work-around as you have done.

Re: Proc SQL order by value not ascii

Ksharp — Thu, 19 Mar 2020 12:03:09 GMT

Try padding white blanks before format.

proc format ;
value valband
0 = '                0'
0<-30 = '      0-30'
30<-90 = '   30-90'
90<-215 = '  90-215'
215<-430= ' 215-430'
430-high = 'over 430';
run ;

Re: Proc SQL order by value not ascii

ballardw — Thu, 19 Mar 2020 15:44:09 GMT

I think you are missing the step that links the numeric value to your val_bin variable. And it isn't needed.

proc sql;
   create table work.want as
   select value,
         case
            when (value=0)            then 1
            when (  0 lt value le 30) then 2
            when ( 30 lt value le 90) then 3
            when ( 90 lt value le 215)then 4
            when (215 lt value le 430)then 5
            when (430 lt value)       then 6
            else .
         end  as Valband
  from have
  ;
quit;

Or read the data with an informat at an earlier step.

Re: Proc SQL order by value not ascii

cactooos — Thu, 19 Mar 2020 18:36:43 GMT

@PaigeMiller I might use the output directly from proc sql so was looking for some kind of workaround like this
@ballardw I am using two variables: value and value_band with the format valueband. which I created at the beginning, I'm doing some grouping and classifications on the way so the question was stricte about the order of these groups but thank you for your point

Re: Proc SQL order by value not ascii

PaigeMiller — Thu, 19 Mar 2020 18:42:41 GMT

@cactooos wrote:

@PaigeMiller I might use the output directly from proc sql so was looking for some kind of workaround like this

In general, for all but the simplest problems, PROC SQL is not a good tool for creating tables. I know people use it for that, but its not a good choice, and its not what PROC SQL was designed to do. PROC REPORT has so many more features to help you get the right report, and to obtain the right report with much less coding than would be required in PROC SQL (for all but the simplest problems).

Re: Proc SQL order by value not ascii

ballardw — Thu, 19 Mar 2020 19:10:30 GMT

When you have a format available for a variable then frequently there is no reason needed to create a text version of the variable. The group created by the format will be honored by almost all the SAS reporting, analysis and graphing procedures. I say "almost all" as I don't use every single procedure but the ones I use the formatted value works just fine.

Here's an example of using two different formats on the same variable to create two similar reports with different groups:

proc format library=work;
value age3grp
10 - 12 = '10-12 Years'
13 - 15 = '13-15 Years'
16 - high= '16+ Years'
;
value age2grp
10 - 13 = '10-13 Years'
13 - high= '13+ Years'
;
run;

proc report data=sashelp.class;
   columns sex age height,(n pctn);
   define sex / group ;
   define age / group format=age3grp.;
   define height/analysis;
run;
proc report data=sashelp.class;
   columns sex age height,(n pctn);
   define sex / group ;
   define age / group format=age2grp.;
   define height/analysis;
run;

You should have the Sashelp.class data set available to run the code.

Not having to add variables and just apply a different format is a very powerful tool.