Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SAS dataset layout change

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1522
Location: Chennai

PostPosted: Tue Oct 20, 2009 4:31 pm    Post subject: SAS dataset layout change
Reply with quote

Hi,

I have a input SAS dataset as shown below,
Code:
depot   region    jobname elapsed     
                                
HI      MVSD      BK10   2.32 MINUTES
HI      MVSD      BK15   0.03 MINUTES
HI      MVSD      EODB   1.02 MINUTES
LC   MVSE      BK10   2.32 MINUTES
LC   MVSE     BK15   0.03 MINUTES
LC   MVSE      EODB   1.02 MINUTES

The requirement is to convert this dataset to single variable for each occurence of depot variable, as shown below,

Code:
depot   region   jobname   elapsed           jobname   elapsed           jobname   elapsed
HI   MVSD   BK10   2.32 MINUTES   BK15   0.03 MINUTES   EODB   1.02 MINUTES
LC   MVSE   BK10   2.32 MINUTES   BK15   0.03 MINUTES   EODB   1.02 MINUTES

I have tried my luck with PROC TRANSPOSE, but unable to solve this. sterb050.gif
Could you please shed some light in how to go about this problem pls.


P.S. - I battled with the screen prints above for 15 mins and somehow aligned them a little.

Thanks in advance,
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8165
Location: East Dubuque, Illinois, USA

PostPosted: Tue Oct 20, 2009 4:40 pm    Post subject:
Reply with quote

PROC TRANSPOSE flips columns and rows -- not at all what you want.

Are there always three observations per depot variable, or can the number vary? If it can vary, what is the maximum number you expect?

This can be done with arrays or with named variables but you haven't provided enough information yet.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1522
Location: Chennai

PostPosted: Tue Oct 20, 2009 4:54 pm    Post subject:
Reply with quote

Robert,
Thanks for the swift response, there are only 3 observations for each occurence of depot variable.
The region column will be the same for the 3 observations under each occurence of depot variable.

I am trying to construct a CSV report from the output dataset which needs to be sent as attachment.
I am ok with the emailing part, but the reformatting thing is a little confusing icon_eek.gif as I have not used SAS for quite a while.

Pls let me know if any more info is required.

Thanks in advance,
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Oct 20, 2009 5:56 pm    Post subject:
Reply with quote

For things like this, I usually use a building process as shown below.

Code:

//CARDSIN   DD *                         
HI      MVSD      BK10   2.32 MINUTES     
HI      MVSD      BK15   0.03 MINUTES     
HI      MVSD      EODB   1.02 MINUTES     
LC      MVSE      BK10   2.32 MINUTES     
LC      MVSE      BK15   0.03 MINUTES     
LC      MVSE      EODB   1.02 MINUTES     
/*                                       
//SYSIN     DD *                         
OPTIONS NOCENTER SOURCE SOURCE2;         
DATA OUT01;                               
  INFILE    CARDSIN;                     
  INPUT     @01   DEPOT    $CHAR2.       
            @09   REGION   $CHAR4.       
            @19   JOBN     $CHAR4.       
            @26   TIMER    $CHAR14.;     
  RUN;                                   
                                         
PROC SORT DATA=OUT01 NODUPS;             
  BY DEPOT REGION;                       
  RUN;                                             
                                                   
DATA _NULL_;                                       
  LENGTH  CDEOUT $80.;                             
  RETAIN  CDEOUT ' ';                             
  FILE  FILEOUT;                                   
  SET OUT01;                                       
  BY DEPOT REGION;                                 
  IF FIRST.REGION THEN DO;                         
    CDEOUT = DEPOT || ',' || REGION;               
  END;                                             
                                                   
  ABCOUT = ','||TRIM(JOBN) || ',' || TRIM(TIMER); 
  CDEOUT = TRIM(CDEOUT) || TRIM(ABCOUT);           
                                                   
  IF LAST.REGION THEN DO;                         
    PUT CDEOUT;                                   
    CSVOUT = ' ';                                 
  END;                                             


And this is the output - csv format
Code:

HI,MVSD,BK10,2.32 MINUTES,BK15,0.03 MINUTES,EODB,1.02 MINUTES
LC,MVSE,BK10,2.32 MINUTES,BK15,0.03 MINUTES,EODB,1.02 MINUTES
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8165
Location: East Dubuque, Illinois, USA

PostPosted: Tue Oct 20, 2009 6:13 pm    Post subject:
Reply with quote

Code:
DATA INDATA;
     INFILE ????????;
     INPUT DEPOT $ REGION $ JOB $ ELAPSED ;
DATA MERGED;
     SET INDATA ;
     BY DEPOT;
     RETAIN DEPOT R1 J1 E1 R2 J2 E2 ;
     IF FIRST.DEPOT THEN DO; R1=REGION; J1=JOB; E1=ELAPSED; END;
     ELSE IF LAST.DEPOT THEN DO; R3=REGION; J3=JOB; E3=ELAPSED; OUTPUT; END;
     ELSE DO; R2=REGION; J2=JOB; E2=ELAPSED; END;
will generate a SAS data set MERGED with one observation per depot (assuming you replace the ???????? with the file DD).
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1522
Location: Chennai

PostPosted: Tue Oct 20, 2009 6:14 pm    Post subject:
Reply with quote

Many Thanks Expat & Robert for the smart logic.
It WORKED!!!! icon_biggrin.gif

Regards,
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8165
Location: East Dubuque, Illinois, USA

PostPosted: Tue Oct 20, 2009 6:20 pm    Post subject:
Reply with quote

Glad to hear it worked. icon_smile.gif
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Oct 20, 2009 6:26 pm    Post subject:
Reply with quote

Robert Sample wrote:
Glad to hear it worked. icon_smile.gif


Hey Robert, that is so sneaky, writing some code and then getting someone on the forum to test it for you icon_lol.gif icon_lol.gif icon_lol.gif
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1522
Location: Chennai

PostPosted: Tue Oct 20, 2009 6:29 pm    Post subject:
Reply with quote

Quote:
TANSTAAFL


TANSTAAFTesting too.. it will be 10$ pls.

icon_wink.gif
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8165
Location: East Dubuque, Illinois, USA

PostPosted: Tue Oct 20, 2009 6:30 pm    Post subject:
Reply with quote

expat, me? sneaky? Actually, that code was tested before I posted it -- so I knew it worked! icon_biggrin.gif
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 -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts How to change a string containing the... chong.zhou TSO/ISPF 6 Tue Aug 29, 2017 4:54 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us