topic proc sql and data step for testing duplications are giving differet numbers in SAS Programming

proc sql and data step for testing duplications are giving differet numbers

mamin088 — Tue, 04 Jun 2019 17:33:33 GMT

Hi everybody, I'm trying to find the duplications in a data set using two different codes. But I get different #observations! Would somebody explain it to me why?! here are the codes:

proc sql; create table SINGLE as select unique VARIABLE from DATASET; quit; run;

proc sort data=DATASET; by VARIABLE; run;

data dup nodup blank;

set DATASET;

by VARIABLE;

if first.VARIABLE and last.VARIABLE then output nodup;

else if VARIABLE="" then output blank;

else output dup;

run;

but I get different # of observations for table SINGLE and data dup.

Thank you!

Re: proc sql and data step for testing duplications are giving differet numbers

Tom — Tue, 04 Jun 2019 17:40:14 GMT

Your two codes are doing totally different things.

To replicate what your SQL code is doing just use FIRST.VARIABLE.

data dup nodup blank single2(keep=variable);
  set DATASET;
  by VARIABLE;

  if first.VARIABLE then output single2;

  if first.VARIABLE and last.VARIABLE then output nodup;
  else if VARIABLE="" then output blank;
  else output dup;

run;

Re: proc sql and data step for testing duplications are giving differet numbers

VDD — Tue, 04 Jun 2019 17:41:08 GMT

that is because this code

if first.VARIABLE and last.VARIABLE then output nodup;
else if VARIABLE="" then output blank;
else output dup;

will not be the same as this code

unique VARIABLE

why are you using unique rather that distinct ?

Re: proc sql and data step for testing duplications are giving differet numbers

mamin088 — Tue, 04 Jun 2019 17:51:58 GMT

Thank you! I did that and you are right the
if first.VARIABLE then output single2; replicates the SQL code but why? I don't understand the difference? SQL is supposed to give me the distinct observations.

Re: proc sql and data step for testing duplications are giving differet numbers

mamin088 — Tue, 04 Jun 2019 17:52:25 GMT

Thank you VDD! I just searched and found that sql unique is an old syntax and is not an standard one anymore. Better to use the distinct. Thank you again!

Re: proc sql and data step for testing duplications are giving differet numbers

Tom — Tue, 04 Jun 2019 18:11:50 GMT

The DISTINCT is giving you distinct OBSERVATIONS. But since your select statement only has one variable the effect is distinct VALUES of that variable. No matter how many times that value appears the output includes it only once.

In your data step FIRST.VARIABLE and LAST.VARIABLE will be true for values of VARIABLE that appear only ONCE in the data. The values that appear multiple times will be written to one of the other datasets.

Consider this example:

data have;
  input variable ;
cards;
1
1
2
3
3
4
;

The distinct set of values is 1,2,3,4. The values that appear only once are 2 and 4.

Re: proc sql and data step for testing duplications are giving differet numbers

mamin088 — Tue, 04 Jun 2019 18:16:52 GMT

Got it! Thank you so much for your time!