Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

convert columns to rows in a dataset

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
surya_pathaus

Active User


Joined: 28 Aug 2006
Posts: 110

PostPosted: Tue Dec 30, 2008 9:15 am    Post subject: convert columns to rows in a dataset
Reply with quote

I have a dataset which has a single column. I need to convert the data from this column into a row with comma seperated.

Example:

File One(Input file) contains records as below:

A
B
C
D
My output should be :
A,B,C,D

Please let me know your suggestions/ideas.
Back to top
View user's profile Send private message

krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Dec 30, 2008 1:57 pm    Post subject:
Reply with quote

surya_pathaus

What the LRECL and RECFM of i/p and o/p files?
Is that first four records of the i/p to be converted to a row in the o/p and then the second four records of i/p file to second row in the o/p file... and so on?
Back to top
View user's profile Send private message
surya_pathaus

Active User


Joined: 28 Aug 2006
Posts: 110

PostPosted: Tue Dec 30, 2008 3:14 pm    Post subject: Reply to: convert columns to rows in a dataset
Reply with quote

Kris,

Record Length of Input - 80
Record Length of Output - 100

I want all the records from the column need to convert into one row.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Dec 30, 2008 5:06 pm    Post subject:
Reply with quote

surya_pathaus
I have considered 4 records in the i/p file and built this ICETOOL cards. Please modify accordingly.
Code:
//*******************************************************       
//STEP001  EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1      DD *                                                 
A                                                               
B                                                               
C                                                               
D                                                               
*                                                               
//OUT      DD SYSOUT=*                                         
//TOOLIN   DD *                                                 
 SPLICE FROM(IN1) TO(OUT) ON(80,1,CH)  -                       
  WITHEACH   WITH(2,2) WITH(4,2) WITH(6,2) WITH(8,2) USING(CPY1)
/*                                                             
//CPY1CNTL DD  *                                               
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(101:SEQNUM,4,ZD)),           
       IFTHEN=(WHEN=(101,4,ZD,EQ,2),OVERLAY=(2:C',',1,1)),     
       IFTHEN=(WHEN=(101,4,ZD,EQ,3),OVERLAY=(4:C',',1,1)),     
       IFTHEN=(WHEN=(101,4,ZD,EQ,4),OVERLAY=(6:C',',1,1)),     
       IFTHEN=(WHEN=(101,4,ZD,EQ,5),OVERLAY=(8:C',',1,1))       
 OUTFIL BUILD=(1,100) 


o/p looks like:
Code:
A,B,C,D,*
Back to top
View user's profile Send private message
samuel_Inba

New User


Joined: 03 Jan 2008
Posts: 53
Location: Chennai

PostPosted: Fri Jan 09, 2009 4:56 pm    Post subject:
Reply with quote

Hi,
Is there any way vice versa. I have a requirement were i need to convert the rows to columns.

Pls suggest.


Thanks,
Sam.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Fri Jan 09, 2009 5:56 pm    Post subject:
Reply with quote

SAS PROC TRANSPOSE is a way to do this but it's not part of the SORT package.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jan 09, 2009 10:18 pm    Post subject:
Reply with quote

Samuel,

Please start a NEW topic for your question. Show an example of your input records and what you expect for output. Give the relevant information (RECFM, LRECL, layout of input and output fields, etc).
Back to top
View user's profile Send private message
surya_pathaus

Active User


Joined: 28 Aug 2006
Posts: 110

PostPosted: Wed Mar 11, 2009 3:12 am    Post subject: Reply to: convert columns to rows in a dataset
Reply with quote

Hi,

Quote:
//*******************************************************
//STEP001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
A
B
C
D
*
//OUT DD SYSOUT=*
//TOOLIN DD *
SPLICE FROM(IN1) TO(OUT) ON(80,1,CH) -
WITHEACH WITH(2,2) WITH(4,2) WITH(6,2) WITH(8,2) USING(CPY1)
/*
//CPY1CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(101:SEQNUM,4,ZD)),
IFTHEN=(WHEN=(101,4,ZD,EQ,2),OVERLAY=(2:C',',1,1)),
IFTHEN=(WHEN=(101,4,ZD,EQ,3),OVERLAY=(4:C',',1,1)),
IFTHEN=(WHEN=(101,4,ZD,EQ,4),OVERLAY=(6:C',',1,1)),
IFTHEN=(WHEN=(101,4,ZD,EQ,5),OVERLAY=(8:C',',1,1))
OUTFIL BUILD=(1,100)




I was using the above ICETOOL "SPLICE" code, now I got an issue with the above code.

When my Input contain only single record then "SPLICE" is returning empty dataset as an output.

In IN1 (input) contains only one record i.e,
A
then the above code is creating empty output dataset. But I need A to be copied into output dataset.

Please suggest.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Mar 11, 2009 3:25 am    Post subject: Reply to: convert columns to rows in a dataset
Reply with quote

surya_pathaus,

Add the parm KEEPNODUPS

change this line in the job

Quote:

SPLICE FROM(IN1) TO(OUT) ON(80,1,CH) -


to

Quote:

SPLICE FROM(IN1) TO(OUT) ON(80,1,CH) KEEPNODUPS -
Back to top
View user's profile Send private message
surya_pathaus

Active User


Joined: 28 Aug 2006
Posts: 110

PostPosted: Wed Mar 11, 2009 3:29 am    Post subject: Reply to: convert columns to rows in a dataset
Reply with quote

Thanks alot.
Back to top
View user's profile Send private message
shr_amar
Warnings : 2

Active User


Joined: 02 May 2005
Posts: 126
Location: UK

PostPosted: Mon Mar 15, 2010 5:59 pm    Post subject:
Reply with quote

What changes we need to do when input is a VB file with RL 84 and output file is having same RECFM *FB 100
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Mar 15, 2010 6:04 pm    Post subject:
Reply with quote

shr_amar wrote:
What changes we need to do when input is a VB file with RL 84 and output file is having same RECFM *FB 100


Instead why don't you just study what is basic differences in VB and FB datasets.
You will easily understand changes needed.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Mar 15, 2010 10:33 pm    Post subject:
Reply with quote

Quote:
What changes we need to do when input is a VB file with RL 84 and output file is having same RECFM *FB 100


It depends on what you're trying to do. Please show an example of the records in your input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input file. If the input file can have duplicates, show that in your example.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Mon Mar 15, 2010 10:57 pm    Post subject:
Reply with quote

Frank,

You are heading down a road containing a moving target of specifications.

This particular thread was resurected after one year with only a mininal amount of information.

Additionally, look at two recent and similar threads by the same poster.

Same story: 'what if this', 'no, what about that'.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Mar 15, 2010 11:43 pm    Post subject:
Reply with quote

dave,

Thanks for the warning. Yes, I've noticed that shr_amar's posts are kind of "hit and run". If he provides the information I need, I'll try to help. If not, ...
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Nov 02, 2010 11:43 pm    Post subject:
Reply with quote

With z/OS DFSORT V1R10 PTF UK90025 or z/OS DFSORT V1R12 PTF UK90026 (Oct,2010), you can now do this kind of task more easily with the new RESIZE operator of DFSORT's ICETOOL. For example:


Code:

//S1  EXEC  PGM=ICETOOL                                       
//TOOLMSG DD SYSOUT=*                                           
//DFSMSG  DD SYSOUT=*                                           
//SYSUDUMP DD SYSOUT=*                                         
//IN DD *                                                       
A                                                               
B                                                               
C                                                               
D                                                               
*                                                               
//OUT DD SYSOUT=*     
//TOOLIN DD *                                                   
RESIZE FROM(IN) TO(OUT) TOLEN(100) USING(CTL1)                 
//CTL1CNTL DD *                                                 
  INREC BUILD=(1,2)                                             
  OUTFIL FNAMES=OUT,OVERLAY=(1:1,100,SQZ=(SHIFT=LEFT,MID=C','))


OUT would have:

Code:

A,B,C,D,*


For complete details on the new functions for DFSORT and DFSORT's ICETOOL available with the Oct, 2010 PTF, see:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000242
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm
No new posts convert PD to ZD ram_vizag DFSORT/ICETOOL 5 Sat Nov 05, 2016 3:06 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us