Merging two files by column



Support for OS/VS COBOL, VS COBOL II, COBOL for OS/390 & VM and Enterprise COBOL for z/OS

Re: Merging two files by column

Postby NicC » Fri Sep 28, 2012 2:46 am

All you need to know about JCL is in the JCL manuals, COBOL in the COBOL manuals, DFSORT (of which ICETOOL is a part) in the DFSORT manuals, SYNCSORT (and SYNCTOOL) in the SYNCSORT manuals. The latter are only available to you if your site is licensed to use SYNCSORT. All the other are available via the Manuals link at the top/bottom of the page or via the IBM website.

Ususally a site uses DFSORT OR SYNCSORT. Although they have similar functions and control cards they are not identical so it is necessary to tell those who can give a solution which you are using. SYNCSORT gives a message at the top of its output messages, DFSORT does the same but in addition it has a code on one of its messages which tells at what level the particular release you are using.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Re: Merging two files by column

 

Re: Merging two files by column

Postby dick scherrer » Fri Sep 28, 2012 3:22 am

Hello,

I didin't realise that SORT and DFSort utilities aren't JCL.
Lots of new people do not. and to make things worse, some trainers are telling students that sort, utilities, etc ARE jcl which causes no end of confusion.

There are examples in both parts of the forum (for DFSORT and Syncsort) that show examples of people who have done what you want. Suggest you look for JOINKEYS using the forum search feature. If you wanted a cobol solution, there is working (tested) code available in a "Sticky" near the top of the COBOL part of the forum (this is called a 2-file match/merge).
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Merging two files by column

Postby ehofas » Fri Sep 28, 2012 3:42 pm

I managed to join the records with joinfields. Thanks for the help. Now I need to merge two files like that..:
FILE1:
00000004  STANISLAV 04 862341224
00000005  RONALDAS  04 862641332
00000006  TOMAS     04 862241348
00000013  ANIRUDDHA 04 862335344
00000014  RONY      04 862314344
00000019  VENKAT    04 861341334
00000024  RUTA      04 862845322
00000025  VYTAUTAS  04 862361028
00000026  GIEDRE    04 862330944
00000033  GABIJA    04 862343884
00000034  SAULIUS   04 862341364


FILE2:
00000001  $100.000  RIMI      REJECTED  01
00000003  $300.000  RIMI      REJECTED  03
00000007  $150.000  MAXIMA    REJECTED  02
00000008  $160.000  MAXIMA    REJECTED  02
00000010  $700.000  MAXIMA    REJECTED  01
00000011  $600.000  MAXIMA    REJECTED  02
00000013  $400.000  RIMI      REJECTED  04
00000014  $300.000  RIMI      REJECTED  04
00000020  $060.000  RIMI      REJECTED  03
00000021  $050.000  MAXIMA    REJECTED  01
00000025  $140.000  RIMI      REJECTED  04
00000025  $001.000  MAXIMA    REJECTED  04
00000025  $020.000  RIMI      REJECTED  04
00000026  $150.000  RIMI      REJECTED  04
00000030  $070.000  MAXIMA    REJECTED  01
00000032  $100.300  MAXIMA    REJECTED  03
00000033  $100.400  MAXIMA    REJECTED  04
00000035  $206.000  MAXIMA    REJECTED  02
00000036  $120.000  RIMI      REJECTED  02
00000037  $200.000  RIMI      REJECTED  03
00000040  $100.000  MAXIMA    REJECTED  03

OUTFILE:
00000013,ANIRUDDHA,04,862335344
$400.000,RIMI,REJECTED
00000014,RONY,04,862314344
$300.000,RIMI,REJECTED
00000025,VYTAUTAS,04,862361028
$140.000,RIMI,REJECTED
$001.000,MAXIMA,REJECTED
$020.000,RIMI,REJECTED
00000026,GIEDRE,04,862330944
$150.000,RIMI,REJECTED
00000033,GABIJA,04,862343884
$100.400,MAXIMA,REJECTED

I managed to get the following output file:
00000013,ANIRUDDHA,04,862335344,$400.000,RIMI,REJECTED
00000014,RONY,04,862314344,$300.000,RIMI,REJECTED
00000025,VYTAUTAS,04,862361028,$140.000,RIMI,REJECTED
00000025,VYTAUTAS,04,862361028,$001.000,MAXIMA,REJECTED
00000025,VYTAUTAS,04,862361028,$020.000,RIMI,REJECTED
00000026,GIEDRE,04,862330944,$150.000,RIMI,REJECTED
00000033,GABIJA,04,862343884,$100.400,MAXIMA,REJECTED


USING THIS:
//xxxxxx JOB (KOM,RA40),'MERGE',MSGCLASS=X,CLASS=X,
//  NOTIFY=&SYSUID
//STEP02 EXEC PGM=SORT
//SORTIN DD DSN=xxxx.VSAM4,DISP=SHR
//SORTIN1 DD DSN=xxxx.REJECT,DISP=SHR
//SORTOUT DD DSN=xxxx.MERGED4,DISP=(NEW,CATLG,DELETE),
//   LRECL=80,RECFM=FB,SPACE=(TRK,(1,1))
//SYSOUT DD SYSOUT=*
//SYSIN DD *
 JOINKEYS F1=SORTIN,FIELDS=(1,8,A)
 JOINKEYS F2=SORTIN1,FIELDS=(1,8,A)
 REFORMAT FIELDS=(F1:1,33,F2:11,30)
 SORT FIELDS=COPY
 INREC PARSE=(%00=(ENDBEFR=C'#',FIXLEN=61),
            %01=(ENDBEFR=C'#',FIXLEN=61),
            %02=(ENDBEFR=C'#',FIXLEN=61),
            %03=(ENDBEFR=C'#',FIXLEN=61),
            %04=(ENDBEFR=C'#',FIXLEN=61),
            %05=(ENDBEFR=C'#',FIXLEN=61),
            %06=(ENDBEFR=C'#',FIXLEN=61)),
      BUILD=(%00,%01,%02,%03,%04,%05,C'''',%06,C'''')

  OUTREC IFTHEN=(WHEN=INIT,
  BUILD=(1,140,SQZ=(SHIFT=LEFT,PAIR=APOST,MID=C','))),
  IFTHEN=(WHEN=INIT,FINDREP=(IN=C'''',OUT=C''))
/*


Basicly, what I want to do is merge these two files, leaving only one line of information about a person(00000013,ANIRUDDHA,04,862335344) and in the other following lines I want to list all the actions done by this particular person($400.000,RIMI,REJECTED), like in the output file. I'm afraid that I will not be able to do this using sort. Am I supposed to use ICETOOL for this to be done? Hope you guys have any ideas, thanks.

Code'd
ehofas
 
Posts: 6
Joined: Thu Sep 27, 2012 6:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging two files by column

Postby BillyBoyo » Fri Sep 28, 2012 6:40 pm

You still didn't say which Sort product you use.

Do you just want "comma separated" fields? Can there be any embedded blanks in your fields? What is C'#' doing for you and why do you believe your fields are up to 61 characters long in variable position, when they are in fixed positions, so therefore known lengths.

You should be able to use WHEN=GROUP to PUSH a sequence number. You should then be able to tell if you are processing the first of a group on the key, or the subsequent, allowing you to do your different outputs.

You are losing data from file 2. I don't think it is a good idea (confusing at best) to use SORTIN and SORTIN1 for your filenames.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging two files by column

Postby ehofas » Mon Oct 01, 2012 12:56 pm

I want just comma separated fields. There shouldn't be blanks. What are the other ways I can describe the length of the field if it is in fixed position? How would syntax look for using WHEN=GROUP, and what does the RECORD='number' mean? What should it be set to? I do not need all the data from file 2, so losing that data is not a problem, and yes, it is a bit confusing because of the file names. Thanks for your answer!
ehofas
 
Posts: 6
Joined: Thu Sep 27, 2012 6:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging two files by column

Postby BillyBoyo » Mon Oct 01, 2012 1:38 pm

Can you please finally tell us which Sort product you use so that this can be moved out of Cobol and given the appropriate attention. No more questions will be answered until that is done.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging two files by column

Postby ehofas » Mon Oct 01, 2012 1:47 pm

I'm sorry. I'm using ICEMAN for my current task.. I'm just not 100% sure if that is the correct program to use.
ehofas
 
Posts: 6
Joined: Thu Sep 27, 2012 6:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging two files by column

Postby BillyBoyo » Mon Oct 01, 2012 1:56 pm

OK, but SyncSort can be "aliased" to ICEMAN and SyncTool can be "aliased" to ICETOOL.

So, can you tell us the message-prefix in the sysout from a Sort step. ICE* or WER*.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Previous

Return to IBM Cobol

 


  • Related topics
    Replies
    Views
    Last post