topic Re: proc sort nodupkey in SAS Programming

proc sort nodupkey

Ronein — Tue, 20 Jul 2021 10:01:09 GMT

Hello

I have a data set with one field and 2 rows.

In both rows there is same value "601_15013150_10_3021_604E3"

I am doing proc sort nodupkey and I still get 2 rows!!

May anyone help to solve the problem??

Re: proc sort nodupkey

andreas_lds — Tue, 20 Jul 2021 10:11:28 GMT

Please post data in usable form and show the log.

Re: proc sort nodupkey

Patrick — Tue, 20 Jul 2021 10:16:56 GMT

You don't provide any code nor sample data. Proc Sort Nodupkey just works - see below. If you don't get the expected result then either the data is different or something in your code isn't as it should.

For the data: Leading blanks or some hidden whitespace characters (characters that don't print) could make the strings different.

data have;
  input var $20.;
  output;
  output;
  datalines;
601_15013150_10_3021_604E3
;

proc sort data=have out=want nodupkey;
  by var;
run;

proc print data=want;
run;

Re: proc sort nodupkey

Ronein — Tue, 20 Jul 2021 10:26:48 GMT

So the conclusion is to use proc sort nodupkey on numeric fields or on compressed char (not use it on non compressed chars!!)

Re: proc sort nodupkey

Patrick — Tue, 20 Jul 2021 10:36:51 GMT

@Ronein wrote:
So the conclusion is to use proc sort nodupkey on numeric fields or on compressed char (not use it on non compressed chars!!)

@Ronein No, that's definitely not the conclusion. It's: "Know your Data".

Re: proc sort nodupkey

ballardw — Tue, 20 Jul 2021 17:10:07 GMT

Yep.

I had an analogous behavior of getting "two identical values" (actually more than two, one for each level of a variable).

Traced it back that the file exported by the source for one of the sites collecting the data were exported as " the value" and "the value" for the others. So somehow one site had values with a leading space inserted into all of its values.

But the values in SAS are not the same with a leading space. (Actually this is only one of 100's of cases where the data provided does not actually match the provided data dictionary and field descriptions from that source. They don't update the dictionary as often as they change data fields).