|
View previous topic :: View next topic
|
| Author |
Message |
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Hi all,
Is it possible to write a DB2 equivalent query for PROC SUMMARY...?
If so, could you kindly provide me the inputs about this topic...
I have a file which contains around 20 million records. Huge amount of time is consuming every time I sort these 20 million records and summarize them.
Thanks... |
|
| Back to top |
|
 |
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| If your site is licensed for SAS/ACCESS, there is a DB2 interface directly between SAS and DB2. |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| Is it possible to extract the records via the SQL query in the correct order, whilst marking the SAS output table as SORTED BY ? |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| I suspect so, expat, PROC SQL supports the ORDER BY clause and the SAS data set would therefore be sorted. But I don't have any good way to test it since we're not running DB2. |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Yes, My site contains SAS/Access for DB2.
What is that interface and how can I implement it.. Could you please provide me some details.. |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Go to www.sas.com then click on Support&Training then do some searches for exactly what you need to know. SAS has their complete documentation on their web site, along with Tech Notes, sample code, and a variety of other helpful tidbits. |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Here is my finding..
Proc Summary code:
| Code: |
PROC SORT DATA = WORK.PREMIUM
OUT = WORK.PREMIUM1;
BY YEAR
QUARTER
MONTH
POLICY_CENTER
PARTNER_CODE ;
RUN;
PROC SUMMARY DATA=WORK.PREMIUM1;
OUTPUT OUT=WORK.PREMIUM2 SUM=;
BY YEAR
QUARTER
MONTH
POLICY_CENTER
PARTNER_CODE ;
VAR USD_PREMIUMM
USD_CLAIM;
RUN;
|
DB2 equivalent code:
| Code: |
PROC SQL;
CREATE TABLE WORK.PREMIUM2 AS
Select YEAR,
QUARTER,
MONTH,
POLICY_CENTER,
PARTNER_CODE,
Sum(USD_PREMIUMM),
Sum(USD_CLAIM),
from WORK.PREMIUM1
GROUP BY YEAR,
QUARTER,
MONTH,
POLICY_CENTER,
PARTNER_CODE,
;
QUIT;
|
I tried this but it(query) was unable to handle large data... :-(
Corrections are welcome... |
|
| Back to top |
|
|
|
|
