topic Re: Having multiple conditions in an if/then statement that is looking for duplicates in SAS Programming

Having multiple conditions in an if/then statement that is looking for duplicates

justicetobias — Wed, 17 Jun 2020 12:54:59 GMT

Hi,

I am trying to write a program that is looking at a table with an id, date, provider number, and procedure number.

I want to write a program that identifies when there are rows that have the same (or duplicate) id AND date AND provider number, then output them into a new table.

Any idea how that may be possible?

Re: Having multiple conditions in an if/then statement that is looking for duplicates

PaigeMiller — Wed, 17 Jun 2020 12:56:35 GMT

@justicetobias wrote:

Hi,

I am trying to write a program that is looking at a table with an id, date, provider number, and procedure number.

I want to write a program that identifies when there are rows that have the same id, date, and provider number and output them into a new table.

It would definitely help if you provided us a portion of your data (as working SAS data step code).

What should we do if it is not a duplicate? What about triplicates?

Why do you need them in a separate data set? That's usually not necessary and causes extra work.

Here is code (untested) that identifies duplicates, triplicates, etc.

proc freq data=have;
    table id*date*provider_number/noprint list out=_counts_;
run;

proc sql;
    create table want as select have.*,c.count
        from have left join _counts_ c
        on have.id=c.id and have.date=c.date and have.provider_number=c.provider_number;
quit;

Re: Having multiple conditions in an if/then statement that is looking for duplicates

justicetobias — Wed, 17 Jun 2020 13:14:30 GMT

Than you so much for your help.

A portion of the data would be:

ID Procedure Provider Date

845	1002	4444	12-Dec-19
261	1005	4441	13-Dec-19
326	1006	4445	12-Dec-19
232	1004	4444	12-Dec-19
252	1009	4449	13-Dec-19
948	1003	4446	13-Dec-19
675	1005	4447	13-Dec-19
154	1001	4441	12-Dec-19
852	1008	4447	12-Dec-19
954	1006	4440	12-Dec-19
232	1005	4444	12-Dec-19
232	1005	4445	12-Dec-19
154	1001	4441	13-Dec-19
948	1002	4446	13-Dec-19
675	1006	4447	13-Dec-19
261	1006	4441	12-Dec-19

I don't need a new output table, but the only rows I want are ones that have and type of repeat (duplicate, triplicate, etc.) with id, provider, and date.

The code you provider was super helpful and identified the correct rows! But instead of an output that told me the rows, I would like to only have those rows left in the table.

Re: Having multiple conditions in an if/then statement that is looking for duplicates

PaigeMiller — Wed, 17 Jun 2020 13:32:17 GMT

So when you go to use this table next, for example in PROC MEANS or whatever, you can use something like this

proc means data=want(where=(count>1));

Re: Having multiple conditions in an if/then statement that is looking for duplicates

justicetobias — Wed, 17 Jun 2020 13:40:11 GMT

Thank you! do you know why these error messages would appear?

ERROR: Unresolved reference to table/correlation name have.

ERROR: Expression using equals (=) has components that are of different data types.

Re: Having multiple conditions in an if/then statement that is looking for duplicates

PaigeMiller — Wed, 17 Jun 2020 13:53:21 GMT

Show us the log for these steps, the code, the warning messages, the error messages (in other words, don't chop anything out) by copying the log as text (not a picture) and pasting it into the window that appears when you click on the </> icon.

Re: Having multiple conditions in an if/then statement that is looking for duplicates

justicetobias — Wed, 17 Jun 2020 13:55:22 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 proc import datafile = '/home/u49170471/sasuser.v94/fake_partB.xlsx'

74 out = fake_partB

75 dbms = xlsx

76 replace

77 ;

78 run;

NOTE: The import data set has 16 observations and 4 variables.

NOTE: WORK.FAKE_PARTB data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.01 seconds

memory 2743.15k

OS Memory 33704.00k

Timestamp 06/17/2020 01:54:02 PM

Step Count 226 Switch Count 4

Page Faults 0

Page Reclaims 655

Page Swaps 0

Voluntary Context Switches 31

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

82 proc freq data=fake_partB;

83 table bene*srvc_dt*prvdr_npi/noprint list out=_counts_;

84 run;

NOTE: There were 16 observations read from the data set WORK.FAKE_PARTB.

NOTE: The data set WORK._COUNTS_ has 13 observations and 5 variables.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 1853.75k

OS Memory 33212.00k

Timestamp 06/17/2020 01:54:02 PM

Step Count 227 Switch Count 10

Page Faults 0

Page Reclaims 405

Page Swaps 0

Voluntary Context Switches 46

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 1320

86 proc sql;

87 create table want as select fake_partB.*,c.count

88 from duplicates left join _counts_ c

89 on have.bene=c.bene and have.srvc_dt=c.srvc_dt and have.prvdr_npi=c.prvdr_npi;

ERROR: Unresolved reference to table/correlation name have.

ERROR: Could not expand fake_partB.*, correlation name not found.

ERROR: Expression using equals (=) has components that are of different data types.

ERROR: The following columns were not found in the contributing tables: fake_partB.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

90 quit;

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

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 5970.12k

OS Memory 37296.00k

Timestamp 06/17/2020 01:54:02 PM

Step Count 228 Switch Count 0

Page Faults 0

Page Reclaims 86

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

95 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

107

Re: Having multiple conditions in an if/then statement that is looking for duplicates

PaigeMiller — Wed, 17 Jun 2020 14:01:53 GMT

... by copying the log as text (not a picture) and pasting it into the window that appears when you click on the </> icon.

You forgot that step.

Re: Having multiple conditions in an if/then statement that is looking for duplicates

justicetobias — Wed, 17 Jun 2020 15:41:28 GMT

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc import datafile  =  '/home/u49170471/sasuser.v94/fake_partB.xlsx'
 74          out  =  fake_partB
 75          dbms  =  xlsx
 76          replace
 77          ;
 78         run;
 
 NOTE: The import data set has 16 observations and 4 variables.
 NOTE: WORK.FAKE_PARTB data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              2742.68k
       OS Memory           32168.00k
       Timestamp           06/17/2020 03:01:19 PM
       Step Count                        32  Switch Count  4
       Page Faults                       0
       Page Reclaims                     666
       Page Swaps                        0
       Voluntary Context Switches        30
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 79         
 80         
 81         
 82         proc freq data=fake_partB;
 83             table bene*srvc_dt*prvdr_npi/noprint list out=_counts_;
 84         run;
 
 NOTE: There were 16 observations read from the data set WORK.FAKE_PARTB.
 NOTE: The data set WORK._COUNTS_ has 13 observations and 5 variables.
 NOTE: PROCEDURE FREQ used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1965.90k
       OS Memory           31676.00k
       Timestamp           06/17/2020 03:01:19 PM
       Step Count                        33  Switch Count  10
       Page Faults                       0
       Page Reclaims                     418
       Page Swaps                        0
       Voluntary Context Switches        45
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           1320
       
 
 85         
 86         proc sql;
 87             create table want as select fake_partB.*,c.count
 88                 from fake_partB left join _counts_ c
 89                 on have.bene=c.bene and have.srvc_dt=c.srvc_dt and have.prvdr_npi=c.prvdr_npi;
 ERROR: Unresolved reference to table/correlation name have.
 ERROR: Unresolved reference to table/correlation name have.
 ERROR: Unresolved reference to table/correlation name have.
 ERROR: Expression using equals (=) has components that are of different data types.
 ERROR: Expression using equals (=) has components that are of different data types.
 ERROR: Expression using equals (=) has components that are of different data types.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 90         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              5862.12k
       OS Memory           35760.00k
       Timestamp           06/17/2020 03:01:19 PM
       Step Count                        34  Switch Count  0
       Page Faults                       0
       Page Reclaims                     89
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 91         
 92         
 93         
 94         
 95         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 107        
 
User: u49170471

My apologies. Super new. Still figuring things out.

Re: Having multiple conditions in an if/then statement that is looking for duplicates

mklangley — Wed, 17 Jun 2020 16:14:53 GMT

It looks like you're referring to the alias have in your join condition, though your join uses the aliases fake_partB and c.

proc sql;
 87             create table want as select fake_partB.*,c.count
 88                 from fake_partB left join _counts_ c
 89                 on have.bene=c.bene and have.srvc_dt=c.srvc_dt and have.prvdr_npi=c.prvdr_npi;
 ERROR: Unresolved reference to table/correlation name have.
 ERROR: Unresolved reference to table/correlation name have.
 ERROR: Unresolved reference to table/correlation name have.
 ERROR: Expression using equals (=) has components that are of different data types.
 ERROR: Expression using equals (=) has components that are of different data types.
 ERROR: Expression using equals (=) has components that are of different data types.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 90         quit;

If you use have as the alias for fake_partb, that should work:

from fake_partB have left join _counts_ c

Re: Having multiple conditions in an if/then statement that is looking for duplicates

PaigeMiller — Wed, 17 Jun 2020 16:45:30 GMT

Yes, @mklangley hits the nail on the head. You have to change all occurrences of HAVE to FAKE_PARTB

Re: Having multiple conditions in an if/then statement that is looking for duplicates

smantha — Wed, 17 Jun 2020 16:52:27 GMT

proc sort data = have; 
by id date provider_number;
data want;
set have;
by id date provider_number;
if first.id and last.id then do;
 end;
else do;
 output;
end;
run;

Re: Having multiple conditions in an if/then statement that is looking for duplicates

smantha — Wed, 17 Jun 2020 16:56:27 GMT

My bad the solution will be like

proc sort data = have; 
by id date provider_number;
data want;
set have;
by id date provider_number;
if first.provider_number and last.provider_number then do;
 end;
else do;
 output;
end;
run;

Re: Having multiple conditions in an if/then statement that is looking for duplicates

justicetobias — Wed, 17 Jun 2020 17:13:49 GMT

this is perfect! thank you and @PaigeMiller so very much!