IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

convert columns to rows in a dataset


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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
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
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
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
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: 8697
Location: Dubuque, Iowa, USA

PostPosted: Fri Jan 09, 2009 5:56 pm
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 Developer


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

PostPosted: Fri Jan 09, 2009 10:18 pm
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
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
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
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: 128
Location: UK

PostPosted: Mon Mar 15, 2010 5:59 pm
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
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 Developer


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

PostPosted: Mon Mar 15, 2010 10:33 pm
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: 792
Location: Pennsylvania

PostPosted: Mon Mar 15, 2010 10:57 pm
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 Developer


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

PostPosted: Mon Mar 15, 2010 11:43 pm
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 Developer


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

PostPosted: Tue Nov 02, 2010 11:43 pm
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:

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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Remote Unload of CLOB Columns DB2 6
Search our Forums:

Back to Top