topic Re: Please help me understand this SAS code in SAS Procedures

Please help me understand this SAS code

thnkgreen — Wed, 11 Feb 2015 19:51:31 GMT

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;

Re: Please help me understand this SAS code

Reeza — Wed, 11 Feb 2015 19:55:51 GMT

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.

Re: Please help me understand this SAS code

thnkgreen — Wed, 11 Feb 2015 20:21:40 GMT

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

Re: Please help me understand this SAS code

DBailey — Wed, 11 Feb 2015 20:33:38 GMT

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;

Re: Please help me understand this SAS code

thnkgreen — Thu, 12 Feb 2015 14:48:56 GMT

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.

Re: Please help me understand this SAS code

Char — Thu, 12 Feb 2015 15:16:06 GMT

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;

Re: Please help me understand this SAS code

Reeza — Thu, 12 Feb 2015 15:18:48 GMT

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

Can you have duplicates by dcsadd?

Re: Please help me understand this SAS code

RW9 — Thu, 12 Feb 2015 15:20:02 GMT

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.

Re: Please help me understand this SAS code

thnkgreen — Thu, 12 Feb 2015 15:28:21 GMT

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.

Re: Please help me understand this SAS code

thnkgreen — Thu, 12 Feb 2015 15:46:48 GMT

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

Re: Please help me understand this SAS code

Reeza — Thu, 12 Feb 2015 16:00:58 GMT

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