Help using Base SAS procedures

Please help me understand this SAS code

Reply
Occasional Contributor
Posts: 5

Please help me understand this SAS code

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 Smiley Happy

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;

Super User
Posts: 19,772

Re: Please help me understand this SAS code

Posted in reply to thnkgreen

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.

Occasional Contributor
Posts: 5

Re: Please help me understand this SAS code

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

Super Contributor
Posts: 578

Re: Please help me understand this SAS code

Posted in reply to thnkgreen

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;

Occasional Contributor
Posts: 5

Re: Please help me understand this SAS code

Posted in reply to 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. 

Super User
Posts: 19,772

Re: Please help me understand this SAS code

Posted in reply to thnkgreen

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

Can you have duplicates by dcsadd?

Occasional Contributor
Posts: 5

Re: Please help me understand this SAS code

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.

Super User
Super User
Posts: 7,942

Re: Please help me understand this SAS code

Posted in reply to thnkgreen

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.

Occasional Contributor
Posts: 12

Re: Please help me understand this SAS code

Posted in reply to thnkgreen

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;  

Occasional Contributor
Posts: 5

Re: Please help me understand this SAS code

Posted in reply to 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

Super User
Posts: 19,772

Re: Please help me understand this SAS code

Posted in reply to thnkgreen

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

Ask a Question
Discussion stats
  • 10 replies
  • 410 views
  • 6 likes
  • 5 in conversation