Please help me understand this SAS code

[thnkgreen]

Hello.  I am an absolute beginner with SAS.  I am trying to re-write a section of SAS code to the MS Access SQL equivalent.  Please, will someone explain to me what this code is doing and what the SQL equivalent would be.  Thank you so much

DATA TEMP_PAYMENT_2;

      SET TEMP_PAYMENT_1;

      BY DSCNMBR ICASSUF ICASTYPE MSACOMP DCSPOL DCSCERT DESCENDING DCSADD;

      IF FIRST.DSCNMBR AND FIRST.ICASSUF AND FIRST.ICASTYPE AND FIRST.MSACOMP AND FIRST.DCSPOL

! AND FIRST.DCSCERT;

  RUN;

[Reeza]

Equivalent SAS code:

DATA TEMP_PAYMENT_2;

      SET TEMP_PAYMENT_1;

      BY DSCNMBR ICASSUF ICASTYPE MSACOMP DCSPOL DCSCERT DESCENDING DCSADD;

      IF FIRST.DCSCERT;

  RUN;

Assuming a duplicate record situation its keep only the first record based on the record key combination of:

DSCNMBR ICASSUF ICASTYPE MSACOMP DCSPOL DCSCERT

There isn't an easy way to replicate this in SQL and I wouldn't recommend it.

[thnkgreen]

If you *HAD* to replicate it, what would you do?

[DBailey]

maybe

proc sql;

create table want as select

t1.*

from

     have t1

     inner join (

          select DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT, min(DCSADD) as MinDCSADD

          from have

          group by DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT

          ) t2

     on t1.DSCNMBR = t2.DSCNMBR and t1.ICASSUF = t2.ICASSUF and t1.ICASTYPE = t2.ICASTYPE and t1.MSACOMP = t2.MSACOMP and t1.DCSPOL = t2.DCSPOL

          and t1.DCSCERT = t2.MinDCSADD

;

quit;

[thnkgreen]

Thank you both for your suggestions.  I am still having trouble with this.  I am wanting to re-write this code in MS Access SQL. 

[Reeza]

Does your table have more columns than listed in By statement?

Can you have duplicates by dcsadd?

[thnkgreen]

It does have more columns than listed in the BY statement.  There are 22 columns.  I "think" that duplicates are ok in DCSADD.  The reason that I know the proposed SQL isn't working right is because I know how many records I am supposed to be getting back, 717843.  The original data set is 3823873, then after the SQL code it should be 717843.

[RW9]

I would elaborate on what you are having difficulty with, DBailey's code above should work
---

select

t1.*

from

     have t1

     inner join (

          select DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT, min(DCSADD) as MinDCSADD

          from have

          group by DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT

          ) t2

     on t1.DSCNMBR = t2.DSCNMBR and t1.ICASSUF = t2.ICASSUF and t1.ICASTYPE = t2.ICASTYPE and t1.MSACOMP = t2.MSACOMP and t1.DCSPOL = t2.DCSPOL

          and t1.DCSCERT = t2.MinDCSADD

---

However bear in mind you need to have the data in Access, and change the from statements to point to your data in Access.  If your having issues with Access, then best to ask on a forum dedicated to the application, whilst I have some knowledge of access I wouldn't start using it for anything, its more of a toy database.  Check out MySQL, Postgre, SQLServer etc or one of the many others.  All of them are better than Access in many ways.

[Char]

I think this works in SAS but not sure in Access...

PROC SQL;                                                                                                                              

CREATE TABLE TEMP_PAYMENT_2 AS                                                                                                         

SELECT DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT, MAX(DCSADD) AS DCSADD                                                     

FROM TEMP_PAYMENT_1                                                                                                                    

GROUP BY DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT;                                                                        

QUIT;  

[thnkgreen]

I tried the following code and got back 3823873 records.  It is as if the code didn't do anything.  I am wondering if I should have First in this code somewhere??

select *

from TEMP_PAYMENT_1 t1

inner join (select DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT, min(DCSADD) as MinDCSADD

from TEMP_PAYMENT_1

group by DSCNMBR, ICASSUF, ICASTYPE, MSACOMP, DCSPOL, DCSCERT) t2

on t1.DSCNMBR = t2.DSCNMBR and t1.ICASSUF = t2.ICASSUF and t1.ICASTYPE = t2.ICASTYPE and t1.MSACOMP = t2.MSACOMP and t1.DCSPOL = t2.DCSPOL and t1.DCSCERT = t2.DCSCERT

[Reeza]

First is only valid in SAS data steps and not part of SQL.