Working fine for me even with duplicates. data have_1; input new_id name $1. ; cards; 1 a 2 b 3 c 4 d 1 a 1 b run; data have_2; input new_id; cards; 5 6 7 8 9 ; run; proc sql; create table want as select new_ID from have_1 except select new_ID from have_2; quit; Log: 1455  data have_1; 1456  input new_id name $1. ; 1457  cards; NOTE: The data set WORK.HAVE_1 has 6 observations and 2 variables. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       cpu time            0.00 seconds 1464  run; 1465 1466  data have_2; 1467  input new_id; 1468  cards; NOTE: The data set WORK.HAVE_2 has 5 observations and 1 variables. NOTE: DATA statement used (Total process time):       real time           0.01 seconds       cpu time            0.00 seconds 1474  ; 1475  run; 1476 1477  proc sql; 1478  create table want as 1479  select new_ID from have_1 1480  except 1481  select new_ID from have_2; NOTE: Table WORK.WANT created, with 4 rows and 1 columns. 1482  quit; NOTE: PROCEDURE SQL used (Total process time):       real time           0.01 seconds       cpu time            0.00 seconds ... View more

The reason why it's appearing twice is because of the below code if _N_ = 1 then set BEFORE;  This step copies the results from before and pastes it After data set.     put 'After  IF : ' _ALL_; _ALL_ does the same but it copies the results from AFTER and pastes it again. Fix to the code would be data BEFORE; input v1 v2; datalines; 1 2 ; data AFTER; put 'Before IF: ' _ALL_; set BEFORE; put 'After  IF : ' _ALL_; ; run; proc print; run; ... View more

data have1; input Date Identifier Value; cards; 011980 3424 2.4 021980 3543 4.3 031980 432  5.5 ; run; proc sort data=have1 tagsort; by Identifier; run; data have2; input Date Identifier Value;by date; cards; 1979 432  4.2 1980 4332 1.2 1981 423  2.2 1979 123  2.3 1980 127  2.4 1981 4237 2.5 ; run; proc sort data=have2 tagsort; by date; run; data have3 ; do _n_=1 by 1 until (last.Date); set have2; by date; date=cats(_n_,Date); output; end; run; proc sort data = have3 tagsort; by Identifier; run; data want; merge have1 (in=a)    have3 (in=b); by Identifier; if a=b; run; ... View more

PROC SQL is a procedure where as MERGE is a data step. PROC SQL takes longer time to run than MERGE when performing joins on large data sets. PROC SQL provides other potential advantages for communicating outside the SAS as SQL query is the standard method for specifying data requests from one database to another. PROC SQL is particularly appealing because of its simplicity and readability. ... View more

DATA HAVE1; INPUT SERIES_ID $20.; CARDS; SMU01115000000000001 SMU01122200000000001 SMU01138200000000001 SMU01194600000000001 SMU01200200000000001 SMU01225200000000001 SMU01234600000000001 SMU01266200000000001 SMU01336600000000001 SMU01338600000000001 SMU01021000000000001 ; RUN; DATA HAVE2; INPUT Area_Code $5. Area_Title $25. ; CARDS; 01000 Alabama -- Statewide 01001 Autauga County, Alabama 01003 Baldwin County, Alabama 01005 Barbour County, Alabama 01007 Bibb County, Alabama 01009 Blount County, Alabama 01011 Bullock County, Alabama 01013 Butler County, Alabama 01015 Calhoun County, Alabama 01017 Chambers County, Alabama 01019 Cherokee County, Alabama 01021 Chilton County, Alabama ; RUN; DATA HAVE3; SET HAVE1; LENGTH Area_Code $5.; Area_Code = SUBSTR(SERIES_ID,4,5); DATA WANT; IF 0 THEN SET HAVE2; IF _N_=1 THEN DO; DECLARE HASH HH(DATASET: 'HAVE2'); HH.DEFINEKEY ('Area_Code'); HH.DEFINEDATA('Area_Title'); HH.DEFINEDONE(); DO UNTIL(EOF); SET HAVE3 END=EOF;   IF HH.FIND()=0 THEN OUTPUT; ELSE DO; CALL MISSING(OF Area_Title); OUTPUT; END; END; END; STOP; DROP Area_Code; RUN; PROC PRINT; RUN; ... View more