View previous topic :: View next topic |
Author |
Message |
Sumarao Beginner
Joined: 21 Dec 2005 Posts: 4 Topics: 4
|
Posted: Fri Nov 23, 2007 3:44 am Post subject: SAS : How to compare value from 1 file & insert it in ot |
|
|
Hi All,
I am facing a problem with a SAS program. Any help from your side is appriciated.
Here are the details:
We have two files,
1) PIT FILE : Which contains the details of all employees, LRECL=3000 and about 802842 records. One of the fields of this file is LOA type.
2) ADF FILE: Which has a list of LOA Types and Loa Indicators for each Loa type. There are about 71 LOA values.
Requirement:
From the ADF file we have to select those LOATYPES which have LOAIND=E (currently, LOATYPES 5A and 6A has LOAIND=E).
Then compare the LOATYPE of each record of the PIT file, against the list of LOATYPEs recieved from the ADF file. If the LOAType value of PIT File record and ADF list matches, then select that record from PIT file for the output file, else skip it.
Below is the extract of the program which will perform the above task. Code: |
/* CODE TO GET THE LOATYPE AND LOA INDICATOR FROM ADF FILE * /
DATA LOA1(KEEP = LOATYPE LOAIND);
INFILE ADFFILE;
INPUT
@ LOATYPE @CHAR02.
@ LOAIND @CHAR01.;
/* CODE TO GET THE LOATYPEs WHERE LOA INDICATOR IS E (HERE LOAFILE WILL HAVE 2 VALUES OF 5A & 6A* /
DATA LOAFILE(KEEP = LOATYPE);
SET LOA1;
IF LOAIND = 'E' THEN
OUTPUT LOAFILE;
/* CODE TO COMPARE THE LOATYPE OF ADF FILE WITH THE LOATYPE OF PIT FILE */
DATA OUTPUT;
INFILE PITFILE;
INPUT
@ 1 SERIAL $CHAR06.
@ 7 LOATYPE $CHAR02.
; |
From here I am not sure, how to compare the values of LOAtypes. I tried using array, but its not working.
I tried using MERGE, but as the PIT file huge, its taking a long time.
Can you please suggest any other method for this ?
Thanks in advance.
Suma |
|
Back to top |
|
|
vijay Beginner
Joined: 09 May 2003 Posts: 131 Topics: 64
|
Posted: Fri Nov 23, 2007 3:56 pm Post subject: |
|
|
Try something like this
File1 is big file
File2 is small file,also filter records in file2
Code: |
DATA FILE1;
INFILE CARDS;
INPUT @01 ID1 $CHAR2.
@04 DEPT $CHAR2.
@14 SDATE YYMMDD10.
@30 EDATE YYMMDD10.
@45 PAY 8.2;
DATA FILE2;
INPUT @01 ID2 $CHAR2.;
DATA COMBINE;
SET FILE1;
FOUND = 0;
DO I=1 TO NOBS UNTIL (FOUND) ;
SET FILE2 POINT=I NOBS=NOBS;
IF ID1=ID2 THEN DO;
OUTPUT COMBINE;
FOUND=1;
END;
END;
PROC PRINT DATA=COMBINE LABEL;
TITLE 'PRINT GLDATA RECORDS ' ;
/*
|
|
|
Back to top |
|
|
advoss Beginner
Joined: 23 Aug 2005 Posts: 26 Topics: 0
|
Posted: Mon Nov 26, 2007 8:51 am Post subject: |
|
|
Here is another approach.
Code: |
/* CODE TO GET THE LOATYPE AND LOA INDICATOR FROM ADF FILE */
/* CODE TO GET THE LOATYPEs WHERE LOA INDICATOR IS E */
/* (HERE LOAFILE WILL HAVE 2 VALUES OF 5A & 6A*/
DATA LOA1(KEEP = LOATYPE LOAIND);
INFILE ADFFILE;
INPUT @ LOATYPE @CHAR02.
@ LOAIND @CHAR01.;
if LOAIND = 'E';
run;
/* CODE TO COMPARE THE LOATYPE OF ADF FILE WITH THE LOATYPE OF PIT FILE */
DATA PIT1;
INFILE PITFILE;
INPUT @ 1 SERIAL $CHAR06.
@ 7 LOATYPE $CHAR02.
;
run;
proc sql noprint;
create table output as
select PIT.*
FROM LOA1 AS LOA inner JOIN PIT1 AS PIT
WHERE LOA.LOATYPE = PIT.LOATYPE;
quit;
proc print data=output;
title 'Selected records';
run;
|
_________________ Alan Voss |
|
Back to top |
|
|
|
|