topic Re: proc sql sorting values unexpectedly. in SAS Programming

proc sql sorting values unexpectedly.

frupaul — Thu, 26 Jul 2018 16:27:20 GMT

Hi everyone,

Just a quick question on the use of proc sql. I am trying to create the data set below. It is a list of variables from one of the tables I work with (original table contains 50variables). I have used a data set with an infile statement to create this, saving it in a permanent library. However, when it gets saved in a permanent library, the ordering of the values change, and get sorted in ascending order. However, I have noticed that when a temporary table is created instead with data and infile statements, it is sorted as expected.

source code:

data Axis.Field_names;
length Variables $25;
input Variables $;
cards;
Tenure
Age
Household_Income
Family_Size
Monthly_Rents
;

How can I save it in a permanent location without altering the ordering of the values?

thanks

Re: proc sql sorting values unexpectedly.

Reeza — Thu, 26 Jul 2018 16:29:31 GMT

Never seen that happen. Please post your full code.

Re: proc sql sorting values unexpectedly.

Tom — Thu, 26 Jul 2018 16:33:40 GMT

Something must be changing the order. Perhaps you saved it into something other than a SAS dataset? Like a external database?

Perhaps the way you are viewing/printing the value is sorting them.

If you need to preserve the original order then you should create a variable that does that.

data Axis.Field_names;
  length Varnum 8 Variable $25;
  Varnum+1;
  input Variable $;
cards;
Tenure
Age
Household_Income
Family_Size
Monthly_Rents
;

Re: proc sql sorting values unexpectedly.

frupaul — Thu, 26 Jul 2018 16:34:46 GMT

Hi Reeza,

Code is posted in initial Message.

When I use data statement- Data temporary_table - it arranges the values as expected.

When i use datastatement - Date permanant_table - it stores them in a permanent table and rearranges values alphabetically.

Re: proc sql sorting values unexpectedly.

Reeza — Thu, 26 Jul 2018 16:35:44 GMT

@frupaul wrote:

Hi Reeza,

Code is posted in initial Message.

When I use data statement- Data temporary_table - it arranges the values as expected.

When i use datastatement - Date permanant_table - it stores them in a permanent table and rearranges values alphabetically.

Your title says PROC SQL and I don't see any PROC SQL code in this thread.

Re: proc sql sorting values unexpectedly.

Reeza — Thu, 26 Jul 2018 16:37:45 GMT

If these are variable values then there is no order enforced and SQL does not guarantee to maintain any order. If these are variable names of a data set they will remain in order. SASHELP.VCOLUMN has a list of all the variable names/labels/types that can be used for these types of exercises including a variable Number to help with ordering exercises. If you need a specific order of variable values you have to enforce it with a sort, though a data step will not change the order AFAIK.

Re: proc sql sorting values unexpectedly.

Kurt_Bremser — Thu, 26 Jul 2018 16:48:34 GMT

@frupaul wrote:

Hi Reeza,

Code is posted in initial Message.

When I use data statement- Data temporary_table - it arranges the values as expected.

When i use datastatement - Date permanant_table - it stores them in a permanent table and rearranges values alphabetically.

NO.WAY.

In a

data want;
set have;
run;

the internal order of variables in the dataset is kept as is.

if you think differently, post your WHOLE code you are using to create the permanent table.

Re: proc sql sorting values unexpectedly.

Reeza — Thu, 26 Jul 2018 16:53:54 GMT

Run this and post the output.
If what you're saying is happening, the compare will not be equal. Otherwise, you have some other issue.

data class;
set sashelp.class;
run;

libname demo 'C:\_localdata\temp\';

data demo.class;
set class;
run;

proc sql;
create table demo.class2 as
select *
from class;
quit;

proc compare data=demo.class compare=demo.class2;
run;