|
View previous topic :: View next topic
|
| Author |
Message |
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hi,
I have a SAS dataset which looks like,
| Code: |
DSN VOLSER NEXTVOL
AAAA V0002 V0003
AAAA V0001 V0002
AAAA V0004
AAAA V0003 V0004
BBBB A0001
BBBB A0004 A0003
BBBB A0003 A0002
BBBB A0002 A0001
BBBB A0005 A0004
CCCC K0001
DDDD J000B J000C
DDDD J000C
DDDD J000Z J000B
|
The requirement is to create a variable VOLSEQ which is volume sequence number.
Output required:
| Code: |
DSN VOLSER NEXTVOL VOLSEQ
AAAA V0001 V0002 1
AAAA V0002 V0003 2
AAAA V0003 V0004 3
AAAA V0004 4
BBBB A0005 A0004 1
BBBB A0004 A0003 2
BBBB A0003 A0002 3
BBBB A0002 A0001 4
BBBB A0001 5
CCCC K0001 1
DDDD J000Z J000B 1
DDDD J000B J000C 2
DDDD J000C 3
|
The logic is,
For DSN = AAA, V0004 does not have NEXTVOL, so it is the last VOLSER in the sequence(VOLSEQ=4).
Next, the observation
AAAA V0003 V0004
has V0004 as NEXTVOL, so it is the last but first VOLSER in sequence(VOLSEQ=3).
Next, the observation
AAAA V0002 V0003
has V0003 as NEXTVOL, so its VOLSEQ=2 and so on..
Could you please let me know how this could be done.
Thanks & Regards, |
|
| Back to top |
|
 |
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
I have tried a code to create the VOLSEQS but it makes a lot of passes through the same dataset(more than 4 times for sure),which makes it very ineffecient for large number of datasets.
So was looking for a simpler logic to achieve the same.
| Code: |
LIBNAME XX 'WELLS.SAS.DB1';
DATA DSNVOLS;
INFILE 'WELLS.SORTIN' MISSOVER;
INPUT DSN $4. VOLSER $ NEXTVOL $;
RUN;
PROC SQL NOPRINT;
CREATE TABLE XX.DSNVOLS AS /* CREATE SUBTOTALS FOR EACH DSN */
SELECT *,COUNT(*) AS VOLCOUNT FROM DSNVOLS
GROUP BY DSN;
SELECT COUNT(DISTINCT DSN) INTO :DSNCOUNT FROM XX.DSNVOLS;
QUIT;
DATA VOLSEQS;
RETAIN VOLCOUNT PROCESSED 0;
KEEP DSN VOLSER NEXTVOL VOLSEQ;
DO M = 1 TO &DSNCOUNT.; /* DO FOR ALL INDIVIDUAL DSNS */
PTR = PROCESSED + 1;
SET XX.DSNVOLS POINT = PTR;
DO I = PTR TO PTR+VOLCOUNT-1; /* THIS DO LOOP LOOKS FOR DSN WITH */
SET XX.DSNVOLS POINT = I; /* NEXTVOL = ' ' I.E. LAST VOLSER */
IF NEXTVOL = ' ' THEN DO;
VOLSEQ = VOLCOUNT;
STORE_VOL = VOLSER;
OUTPUT;
END;
END;
IF VOLCOUNT > 1 THEN DO;
DO J = 1 TO VOLCOUNT - 1; /* THIS DO LOOP TAKES LAST VOLSER */
DO I = PTR TO PTR+VOLCOUNT-1;/* AND TRAVERSES OTHER VOLSERS */
SET XX.DSNVOLS POINT = I; /* BASED ON NEXTVOL VARIABLE */
IF NEXTVOL = STORE_VOL THEN DO;
VOLSEQ = VOLSEQ - 1;
STORE_VOL = VOLSER;
OUTPUT;
END;
END;
END;
END;
PROCESSED = PROCESSED + VOLCOUNT; /* KEEP TRACK OF PROCESSED OBS */
END;
STOP;
RUN;
PROC SORT DATA = VOLSEQS ; BY DSN VOLSEQ; RUN;
PROC PRINT DATA = VOLSEQS; RUN; |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Is there some reason this must be done with SAS?
It appears to be something Sort could do rather efficiently once the SAS input is placed in a sequential file ? |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Hi D,
There is no restriction to which programming language this has to be done.
I think I am limiting my chances of getting an answer by mentioning SAS on the title. I would post a topic on DFSORT with the same requirement.
Kindly delete/close/leave this thread as it limits responses.
Regards, |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Vasanth,
Per your request, i'll lock this topic.
d |
|
| Back to top |
|
|
|
|
