topic Re: Unique rows between two files in SAS Enterprise Guide

Unique rows between two files

jen123 — Fri, 18 Jan 2013 00:13:39 GMT

I am trying to get the rows that are NOT in both datasets.

**************************************

proc sql;

create table libname.file_discrepancies as select *

from libname.file_a t1, libname.file_b t2

where GEID.t1 NE GEID.t2;

run;

************************************

However, I get this error which I cannot figure out. GEID exists as numeric in both datasets.

ERROR: Unresolved reference to table/correlation name GEID.

Any advice is appreciated.

Thanks!

Re: Unique rows between two files

art297 — Fri, 18 Jan 2013 00:33:12 GMT

: I don't think your code will work or do what you want, but the error you are getting is because you have the roles reversed between file reference and variable. They should be something like t1.geid, not the way you have them (reversed).

Conversely, something like the following might achieve what you want:

data file_a;

input geid;

cards;

;

data file_b;

input geid;

cards;

;

proc sql;

create table work.file_discrepancies as

(select geid from work.file_a

except

select geid from work.file_b)

union

(select geid from work.file_b

except

select geid from work.file_a)

;

quit;

Re: Unique rows between two files

Linlin — Fri, 18 Jan 2013 00:45:00 GMT

or by merging:

proc sort data=file_a; by geid;

proc sort data=file_b; by geid;

run;

data want;

merge file_a(in=a) file_b(in=b);

by geid;

if a and b then delete;

run;

Re: Unique rows between two files

jen123 — Fri, 18 Jan 2013 14:22:33 GMT

Linlin,

I used your code, which was easier for me to understand considering my SAS knowledge level. Anyway, I got this error: ERROR: BY variables are not properly sorted on data set JEN2.COMBINED_GDW_PRIOR_A. (which is file_b in your code above).

The error pointed out to one specific employee. However, when I look at the data row, it looks right. What does a=1 b=1 mean in the error below?

a=1 b=1 GEID=1010353890 SOEID=SK53890 Full_Name=K,Suresh......(there are 135 columns/variables).

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 104183 observations read from the data set JEN1.COMBINED_GDW_CURRENT_A.

NOTE: There were 35472 observations read from the data set JEN2.COMBINED_GDW_PRIOR_A.

WARNING: The data set JEN1.COMBINED_GDW_DISCREPANCIES may be incomplete. When this step was stopped there were 71599 observations

and 135 variables.

WARNING: Data set JEN1.COMBINED_GDW_DISCREPANCIES was not replaced because this step was stopped.

Re: Unique rows between two files

jen123 — Fri, 18 Jan 2013 14:24:55 GMT

Hi Arthur - what does the "cards" mean?

input geid;

cards;

I tried your code. No errors, but the resutls is only the GEID variable. I want all the columns/variables.

Let's say if the GEID matches between the two files but other variables do not match, would changing the "select GEID" in your proc sql to "select *" give me only the rows where all the variables do not match?

Re: Unique rows between two files

art297 — Fri, 18 Jan 2013 14:41:36 GMT

: Linlin's suggestion included first sorting the two files by geid. Did you do that?

a and b, in Linlin's code are boolean variables, indicating if a record came from one, the other, or both files. They were set with the in= option.

A value of 1 indicates that the record existed in that file.

As for your question about my code, cards (otherwise known as datalines) is simply a way of entering data into SAS.

Re: Unique rows between two files

Linlin — Fri, 18 Jan 2013 14:43:14 GMT

Would you please post your code?

Re: Unique rows between two files

jen123 — Fri, 18 Jan 2013 14:47:46 GMT

Sorry about that. Here is the code:

proc sort data=jen1.combined_gdw_current_a; by geid;

proc sort data=jen2.combined_gdw_prior; by geid;

run;

data jen1.combined_gdw_discrepancies;

merge jen1.combined_gdw_current_a(in=a) jen2.combined_gdw_prior_a(in=b);

by geid;

if a and b then delete;

run;

Re: Unique rows between two files

jen123 — Fri, 18 Jan 2013 14:49:46 GMT

The first column is GEID. So I don't understand what the a= and b= mean? Is it saying that GEID is the 3 variable for this particular employee?

a=1 b=1 GEID=1010353890

Re: Unique rows between two files

art297 — Fri, 18 Jan 2013 14:55:40 GMT

The second file is not the one that you sorted. You sorted jen2.combined_gdw_prior, but used jen2.combined_gdw_prior_a.

Re: Unique rows between two files

jen123 — Fri, 18 Jan 2013 14:59:37 GMT

Sorry for being all over the place. I tried Arthur's advice and it worked. Now, what if I want for two fields (called GEID and GOC) to match. So, I am looking for a dataset where both fields do not match.

Here is my code:

proc sql;

create table jen3.combined_gdw_discrepancies_test as

(select GEID from jen1.combined_gdw_current_a

except

select GEID from jen2.combined_gdw_prior_a)

union

(select GEID from jen2.combined_gdw_prior_a

except

select GEID from jen1.combined_gdw_current_a)

;

quit;

Re: Unique rows between two files

jen123 — Fri, 18 Jan 2013 15:00:55 GMT

The second file is not the one that you sorted. You sorted jen2.combined_gdw_prior, but used jen2.combined_gdw_prior_a.

Thanks for catching that Arthur!

Re: Unique rows between two files

art297 — Fri, 18 Jan 2013 15:06:07 GMT

As long as the files don't contain duplicate records in both files (i.e., resulting in a many-to-many match), I'd just use a variant of Linlin's code. i.e.:

proc sort data=jen1.combined_gdw_current_a; by geid goc; run;

proc sort data=jen2.combined_gdw_prior; by geid goc; run;

data jen1.combined_gdw_discrepancies;

merge jen1.combined_gdw_current_a(in=a) jen2.combined_gdw_prior(in=b);

by geid goc;

if a and b then delete;

run;

Re: Unique rows between two files

jen123 — Fri, 18 Jan 2013 15:10:59 GMT

That worked! How do I mark both you and Linlin with the correct answer for this question?

Thank you Linlin and Arthur!

Re: Unique rows between two files

art297 — Fri, 18 Jan 2013 15:16:43 GMT

You can mark Linlin as "correct" and, if desired, I'd be quite happy with a "helpful" thank you. More importantly, glad to see that you achieved what you needed to accomplish.

Re: Unique rows between two files

art297 — Fri, 18 Jan 2013 15:41:11 GMT

: Hopefully, someone more versed in SQL, than me, can show you how this code SHOULD be written. However, the following two sets of code do the two tasks that you asked about. The good news is that, while probably doing it in a more round about way than necessary, both will work regardless of whether there are any many-to-many situations:

/*create two test files*/

data file_a;

input geid goc x y z;

cards;

1 1 1 2 3

2 1 1 2 3

3 1 1 2 3

4 1 1 2 3

5 1 1 2 3

;

data file_b;

input geid goc a b c;

cards;

1 1 4 5 6

2 2 4 5 6

7 1 4 5 6

8 1 4 5 6

9 1 4 5 6

;

proc sql;

create table work.met_condition as

select * from

(select geid from work.file_a

except

select geid from work.file_b)

union

(select geid from work.file_b

except

select geid from work.file_a)

;

create table work.all_combined as

select * from work.file_a

union

select * from work.file_b

;

create table work.file_discrepancies (drop=b_:) as

select *

from work.met_condition a,

work.all_combined (rename=(geid=b_geid)) b

where a.geid=b.b_geid

;

quit;

proc sql;

create table work.met_condition as

select * from

(select geid,goc from work.file_a

except

select geid,goc from work.file_b)

union

(select geid,goc from work.file_b

except

select geid,goc from work.file_a)

;

create table work.all_combined as

select * from work.file_a

union

select * from work.file_b

;

create table work.file_discrepancies2 (drop=b_:) as

select *

from work.met_condition a,

work.all_combined (rename=(geid=b_geid

goc=b_goc)) b

where a.geid=b.b_geid and

a.goc=b.b_goc

;

quit;