Prev: Loop
Next: correlation coefficient by date and time
From: SAS User on 11 Jun 2010 10:50
Hi

My source datatset test4 is already sorted and I do not want to
change it. When I create a new dataset new dataset is being sorted by
name and I don't want this to happen. Can I stop it from happening?

proc sql;
create table test5 as
select distinct name from test4;
quit;

I need to keep the same order from before I de-duplicated test4.

Thanks
Lee

From: Richard A. DeVenezia on 11 Jun 2010 11:27
On Jun 11, 10:50 am, SAS User <sasuser2...(a)googlemail.com> wrote:
> Hi
>
> My source datatset  test4 is already sorted and I do not want to
> change it. When I create a new dataset new dataset is being sorted by
> name and I don't want this to happen. Can I stop it from happening?
>
> proc sql;
> create table test5 as
> select distinct name  from test4;
> quit;
>
> I need to keep the same order from before I de-duplicated test4.
>
> Thanks
> Lee

SQL is set based, so there is no implicit handling of the row order of
the from tables.

You will need to add an row identifier and utilize it in your query.
Example:

----------
data foo;
do id = 1 to 1000;
x = floor (100*ranuni(1234));
output;
end;
run;

proc sql;
* output rows are ordered by X;
create table distinctX as
select distinct X from foo;

* output rows are ordered by original row order of first appearing X
value;
create table distinctXoriginalOrder as
select X from foo
group by X
having id = min(id)
order by id;
quit;
----------


Richard A. DeVenezia
htttp://www.devenezia.com
From: SAS User on 11 Jun 2010 17:47
thanks Richard.. I will give it a go.

Lee

From: SAS User on 12 Jun 2010 07:18
Richard,

I am sorry but I am not sure where my two example tables test4 and 5
need to be incorporated in your example code.

It is a little advanced to what I currently do so I am struggling
little.

Thanks
Lee
From: Lou on 12 Jun 2010 11:49

"SAS User" <sasuser2010(a)googlemail.com> wrote in message
news:812b59ce-fc81-4d87-bfae-60d9221cbb88(a)g19g2000yqc.googlegroups.com...
> Hi
>
> My source datatset test4 is already sorted and I do not want to
> change it. When I create a new dataset new dataset is being sorted by
> name and I don't want this to happen. Can I stop it from happening?
>
> proc sql;
> create table test5 as
> select distinct name from test4;
> quit;
>
> I need to keep the same order from before I de-duplicated test4.
>

If your dataset TEST4 was sorted by NAME you wouldn't have to ask the
question, so it's a little unclear what you're after. For instance, if the
original dataset has NAME in some order like AABBCCAADD, should your result
be ABCD or BCAD (since you specify "distinct" it couldn't be ABCAD, and if
there weren't duplicate values of NAME you wouldn't have to specify
distinct)?


 |  Next  |  Last
Pages: 1 2
Prev: Loop
Next: correlation coefficient by date and time