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

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.  ... View more

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.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          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              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          85         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: Unresolved reference to table/correlation name have. 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: Expression using equals (=) has components that are of different data types. 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        91         92         93         94         95         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 107        ... View more

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. ... View more

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.  ... View more