proc sql sorting values unexpectedly.

[frupaul]

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

[Reeza]

Never seen that happen. Please post your full code.

[frupaul]

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.

[Reeza]

---

@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.

[Kurt_Bremser]

---

@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.

[Tom · Accepted solution]

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
;

[Reeza]

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.

[Reeza]

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;