CSV file to mainframe

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

CSV file to mainframe


I am writing my first SAS procedure and have been able to extract selected records, reformat, create and output mainframe file and generate a report. The last step is to ceate an output mainframe CSV file from a work SAS file. This file would be later down loaded by users.The book I have to work with is oriented to the PC and nobody else I work with knows SAS.

I added to following to my job.

JCL

000024 //*                                                             

000025 //OUTFILE1 DD DSN=TWFLD.S.NFTEST.WORKCSV,                       

000026 //            DISP=(NEW,CATLG,DELETE),                          

000027 //            SPACE=(CYL,(3,3),RLSE),                           

000028 //            DCB=(LRECL=150,BLKSIZE=0,RECFM=FB)                

000029 //*                                                             

000030 //SYSIN    DD *                                                 

000031 %LET THISYEAR = %SYSFUNC(TODAY(),YEAR4.) ;                     

and added the follwing SAS instructions aat the end of my job.

000240    VAR ACTSTRING BOOK_MTH REPO_MTH PRICUSNM ;                          

000241    LABEL LNTYP     = 'CO CD'                                           

000242          BOOK_MTH  = 'BOOK MTH'                                        

000243          ACTSTRING = 'LOAN NBR'                                        

000244          REPO_MTH  = 'REPO MTH'                                        

000245          PRICUSNM  = 'CUSTOMER NAME' ;                                 

000246                                                                        

000247  COMMENT ******************************************************* ;     

000248  COMMENT * CREATE EXCEL SPREADSHEET FILE FROM SAS WORKIN2 FILE   ;     

000249  COMMENT ******************************************************* ;     

000250                                                                        

000251 ODS CSV BODY= OUTFILE1 ;                                               

000252 PROC PRINT DATA=WORKIN2 NOOBS ;                                        

000253 ODS CSV CLOSE ;                                                        

000254                                                                       

However the PROC PRINT behaves as a regular PROC PRINT and dumps the results in the SASLIST output file.  Clearly The syntax or content or both are not sufficient for what I need

Any help I can get getting this file written to the mainframe would be greatly appreciated.

Thanks


Accepted Solutions
Solution
‎02-17-2014 07:21 PM
Super User
Super User
Posts: 7,079

Re: CSV file to mainframe

Why not just write it using a data step?

data _null_;

  file outfile1 dsd ;

  if _n_=1 then put 'CO CD,BOOK MTH,LOAN NBR,REPO MTH,CUSTOMER NAME' ;

  set workin2;

  put (LNTYP BOOK_MTH  ACTSTRING REPO_MTH PRICUSNM) (Smiley Happy ;

run;

View solution in original post


All Replies
Solution
‎02-17-2014 07:21 PM
Super User
Super User
Posts: 7,079

Re: CSV file to mainframe

Why not just write it using a data step?

data _null_;

  file outfile1 dsd ;

  if _n_=1 then put 'CO CD,BOOK MTH,LOAN NBR,REPO MTH,CUSTOMER NAME' ;

  set workin2;

  put (LNTYP BOOK_MTH  ACTSTRING REPO_MTH PRICUSNM) (Smiley Happy ;

run;

Frequent Contributor
Posts: 83

Re: CSV file to mainframe

Tom,

I have seen you put (Smiley Happy into code twice today and was wondering what it did? I have looked over the various SASUG papers on using the colon, but in the put statement was new to me, what exactly does it do?

Super User
Super User
Posts: 7,079

Re: CSV file to mainframe

: is a format modifier that says to only put out as many characters as needed, rather than the full amount required by the variable length or format. An example I have been using lately is to use this with the $QUOTE. format for printing value of character variables. So put name :$quote. will not include the trailing spaces in the value of NAME inside of the quotes.  ("Alfred" instead of "Alfred    ").

So PUT (a b c) (Smiley Happy ;  says to apply just the : modifier to each variable in the variable list.  So the values will not be padded on the right and since : is NOT really a format it does not change the formats that are attached to variables.  So dates look like dates and user defined formats are applied.

Another one I like to use is the = modifier.  So put (_all_) (=) is very similar to put _all_, but it does not clutter the output with automatic variables like _N_ or _ERROR_.   Also you can use put (_all_) (=/) to have each variable=value pair print on its own line.

Occasional Contributor
Posts: 8

Re: CSV file to mainframe

Thanks agaikn Tom, I have completed my first SAS project to replace an existing job thats going away.

I did have a couple follow up questions on this and have included my SAS source to that end

Because of the strange formats required for a couple of the dates and the need to get at the MM portion of a packed decimal date the only solution I could come up with was to create a temporary external work file in order to re-arrange the data and to re-define that data by inputing the temporary non SAS work file with a different set of definitions on the input statement. I am sure that that is a rubbish way of doing it and if you have any suggestions I would certainly appreciate your insights.

For the reports, I needed to have different titles at a client change so opted for 6 separate reports that essentially concatenate in SASLIST.

Last but not least the CSV file I created was FTP'd to my PC and I was able to import the file successfully to EXCEL and save it as an XLS file. I did notice howver that when compared to the file it will be replacing, the original version was delimited by double quotes and a comma as opposed to just commas in my case. Is that just a style thing or do the double quotes amd comma have some other significance.

Regards

This is probably the most god awful SAS procedure you have ever seen.

000001 //ITSGRPVX JOB ,'SAS RPT1099A',CLASS=T,MSGCLASS=X,NOTIFY=&SYSUID       

000002 //*                                                                    

000003 //******************************************************************** 

000004 //* ITSGRPV.ACLS.SASJOBS(RPT1099A)                                     

000005 //* SAS 1099 REPORT AND EXTRACT                                        

000006 //*     CREATE 1099 NOMAD REPORT REPLACEMENT                           

000007 //*     CREATE COMMA DELIMTITED CSV EXTRACT FILE                       

000008 //******************************************************************** 

000009 //*                                                                    

000010 //PROC0010 EXEC SASPROD,PRMOPTN='NOCENTER LS=77 NODATE'                

000011 //*                                                                    

000012 //LOANEXT  DD DISP=SHR,DSN=PWRGD.V.LOANEXT,AMP=('BUFND=90')            

000013 //*                        PDOXD.S.WOF.WOFCO.SASINPUT(ALT0524)         

000014 //*                                                                    

000015 //WRKFILE1 DD DSN=TWRGD.S.NFTEST.WORKSAS1,                            

000016 //            DISP=(NEW,CATLG,DELETE),                                 

000017 //            SPACE=(CYL,(3,3),RLSE),                                  

000018 //            DCB=(LRECL=200,BLKSIZE=0,RECFM=FB)                       

000019 //*                                                                    

000020 //*                                                                    

000021 //OUTFILE1 DD DSN=TWRGD.S.NFTEST.WORKCSV,                              

000022 //            DISP=(NEW,CATLG,DELETE),                                 

000023 //            SPACE=(CYL,(3,3),RLSE),                                  

000024 //            DCB=(LRECL=150,BLKSIZE=0,RECFM=FB)                       

000025 //*                                                                    

000026 //SYSIN    DD *                                                        

000027  COMMENT * ESTABLISH THIS YEAR AND LAST YEAR CCYY DATES * ;            

000028                                                                        

000029 %LET THISYEAR = %SYSFUNC(TODAY(),YEAR4.) ;                             

000030 %LET LASTYEAR = %EVAL(&THISYEAR - 1) ;                                 

000031                                                                       

000032  COMMENT ********************************************************** ;  

000033  COMMENT * READ 700,000 LOAN EXTRACT FILE AND SELECT REPOSSESSED  * ;  

000034  COMMENT * LOANS FROM LAST YEAR                                   * ;  

000035  COMMENT ********************************************************** ;  

000036                                                                        

000037 DATA  EXTLNEXT ;   INFILE LOANEXT  ;                                   

000038                                                                        

000039 INPUT                                                                  

000040  @    1 CLIENT     $3.                                                 

000041  @    4 APP        $2.                                                 

000042  @    6 LN        $13.                                                 

000043  @   85 OGLDSBDT  PD5.                                                 

000044  @  117 ACTSTA    $1.                                                  

000045  @  120 DATCLOS   PD5.                                                 

000046  @  143 LNTYP     PD2.                                                 

000047  @  154 LNCLSCOD   1.                                                 

000048  @  547 PYFCOFCD   $2.                                                 

000049  @ 2428 PRICUSNM   $30.                                                

000050  @ 2488 PRIADDR1   $30.                                                

000051  @ 2518 PRIADDR2   $30.                                                

000052  @ 2548 PRICITY    $18.                                                

000053  @ 2566 PRISTATE   $3.                                                 

000054  @ 2569 PRIZIP     $9. ;                                               

000055                                                                        

000056 IF    (ACTSTA = '3' OR '4')                                            

000057 AND   PYFCOFCD IN ('IR', 'VR', 'CR', 'UR')                             

000058 AND   DATCLOS > &LASTYEAR.0000                                         

000059 AND   DATCLOS < &THISYEAR.0000                                         

000060 AND   LNCLSCOD = 4 ;                                                   

000061                                                                        

000062  COMMENT ********************************************************** ;  

000063  COMMENT * SORT THE EXTRACTED FILE                                * ;  

000064  COMMENT ********************************************************** ;  

000065                                                                        

000066 PROC SORT DATA=EXTLNEXT ; BY CLIENT LNTYP OGLDSBDT LN ;                

000067                                                                       

000068  COMMENT ********************************************************** ;  

000069  COMMENT * CREATE INITIAL WORK FILE TO UNPACK DATA, SHORTEN THE   * ;  

000070  COMMENT * PACKED DATES FROM 9 TO 8 DIGITS, AND CONCATENATE AND   * ;  

000071  COMMENT * FORMAT THE BK-APP-LOAN FIELDS TO A NEW FIELD DEFINITION* ;  

000072  COMMENT ********************************************************** ;  

000073                                                                        

000074 DATA  _NULL_   ;                                                       

000075  SET  EXTLNEXT     ;                                                   

000076   FILE  WRKFILE1 ;                                                     

000077                                                                        

000078   ACTSTRING=CATX('-',CLIENT,APP,LN) ;                                  

000079                                                                        

000080   PUT                                                                  

000081    @    1 CLIENT        $3.                                            

000082    @    5 LNTYP         3.                                             

000083    @    9 PYFCOFCD      $2.                                            

000084    @   12 OGLDSBDT      8.                                             

000085    @   21 ACTSTRING     $20.                                           

000086    @   42 PRICUSNM      $30.                                           

000087    @   73 DATCLOS       8.                                            

000088    @   82 PRIADDR1      $30.                                           

000089    @  113 PRIADDR2      $30.                                           

000090    @  144 PRICITY       $18.                                           

000091    @  163 PRISTATE      $3.                                            

000092    @  167 PRIZIP        $9. ;                                          

000093                                                                        

000094                                                                        

000095  COMMENT ******************************************************* ;     

000096  COMMENT * READ THE WORK FILE TO ESTABLISH NEW FIELD DEFINITIONS ;     

000097  COMMENT * WHICH ALLOWS ME TO GET AT THE MM PROTION OF THE DATE  ;     

000098  COMMENT * FIELDS IN ORDER TO CREATE THE WIERD BOOK AND REPO     ;     

000099  COMMENT * DATES. THE NEW EXTERNAL FILE WIERD BOOK AND REPO     ;      

000100  COMMENT ******************************************************* ;     

000101                                                                        

000102 DATA  WORKIN1  ;   INFILE WRKFILE1  ;                                  

000103  * *******FILE  WRKFILE2 ;                                             

000104                                                                        

000105 INPUT                                                                  

000106  @    1 CLIENT     $3.                                                 

000107  @    5 LNTYP       3.                                                

000108  @    9 PYFCOFCD   $2.                                                 

000109  @   12 OGLDSBDT    8.                                                 

000110  @   12 OGLDSBCC    4.                                                 

000111  @   16 OGLDSBMM    2.                                                 

000112  @   21 ACTSTRING $20.                                                 

000113  @   42 PRICUSNM  $30.                                                 

000114  @   73 DATCLOS     8.                                                 

000115  @   73 DATCLOSC    4.                                                 

000116  @   77 DATCLOSM    2.                                                 

000117  @   82 PRIADDR1  $30.                                                 

000118  @  113 PRIADDR2  $30.                                                 

000119  @  144 PRICITY   $18.                                                 

000120  @  163 PRISTATE   $3.                                                 

000121  @  167 PRIZIP     $9. ;                                               

000122                                                                        

000123   BOOK_MTH=CATX('/',OGLDSBCC,OGLDSBMM) ;                               

000124   REPO_MTH=CATX('/',DATCLOSC,DATCLOSM) ;                               

000125                                                                        

000126   PUT                                                                  

000127    @    1 CLIENT        $3.                                           

000128    @    4 LNTYP         3.                                             

000129    @    7 ACTSTRING     $20.                                           

000130    @   27 BOOK_MTH      $7.                                            

000131    @   34 REPO_MTH      $7.                                            

000132    @   41 PRICUSNM      $30.                                           

000133    @   71 PRIADDR1      $30.                                           

000134    @  101 PRIADDR2      $30.                                           

000135    @  131 PRICITY       $18.                                           

000136    @  149 PRISTATE      $3.                                            

000137    @  152 PRIZIP        $9. ;                                          

000138                                                                        

000139                                                                        

000140                                                                        

000141 * THE REPORT SECTION IS HERE ;                                         

000142                                                                        

000143 * CLIENT 1 - WORLD OBBI ;                                              

000144                                                                        

000145 TITLE  "1099A &SYSDATE9 AT &SYSTIME " ;                                

000146 TITLE2 "                      WORLD OBBI FINANCIAL CORP" ;             

000147 TITLE3 "           BUSINESS ACCOUNTS REPOSSESSED LAST YEAR &LASTYEAR" ;

000148                                                                        

000149 PROC PRINT DATA=WORKIN1 LABEL;                                         

000150    WHERE CLIENT = '001';                                               

000151    BY CLIENT ;                                                         

000152    ID LNTYP  ;                                                         

000153    VAR ACTSTRING BOOK_MTH REPO_MTH PRICUSNM ;                          

000154    LABEL LNTYP     = 'CO CD'                                           

000155          ACTSTRING = 'LOAN NUMBER'                                     

000156          BOOK_MTH  = 'BOOK MTH'                                        

000157          REPO_MTH  = 'REPO MTH'                                        

000158          PRICUSNM  = 'CUSTOMER NAME' ;                                 

000159                                                                        

000160                                                                        

000161 * CLIENT 18 - WORLD OBBI 2 ;                                        

000162                                                                        

000163 PROC PRINT DATA=WORKIN1 LABEL;                                         

000164  TITLE  "1099A &SYSDATE9 AT &SYSTIME " ;                               

000165  TITLE2 "                      WORLD OBBI 2 FINANCIAL CORP" ;            

000166  TITLE3 "           BUSINESS ACCOUNTS REPOSSESSED LAST YEAR &LASTYEAR" ;

000167    WHERE CLIENT = '018';                                              

000168    BY CLIENT ;                                                         

000169    ID LNTYP  ;                                                         

000170    VAR ACTSTRING BOOK_MTH REPO_MTH PRICUSNM ;                          

000171    LABEL LNTYP     = 'CO CD'                                           

000172          ACTSTRING = 'LOAN NUMBER'                                     

000173          BOOK_MTH  = 'BOOK MTH'                                        

000174          REPO_MTH  = 'REPO MTH'                                        

000175          PRICUSNM  = 'CUSTOMER NAME' ;                                 

000176                                                                        

000177 * CLIENT 30 - VOLVO CAR FINANCIAL SERVICES ;                           

000178                                                                        

000179 PROC PRINT DATA=WORKIN1 LABEL;                                         

000180  TITLE  "1099A &SYSDATE9 AT &SYSTIME " ;                               

000181  TITLE2 "                 VOLVO CAR FINANCIAL SERVICES " ;             

000182  TITLE3 "           BUSINESS ACCOUNTS REPOSSESSED LAST YEAR &LASTYEAR" ;

000183    WHERE CLIENT = '030';                                               

000184    BY CLIENT ;                                                         

000185    ID LNTYP  ;                                                         

000186    VAR ACTSTRING BOOK_MTH REPO_MTH PRICUSNM ;                          

000187    LABEL LNTYP     = 'CO CD'                                          

000188          ACTSTRING = 'LOAN NUMBER'                                     

000189          BOOK_MTH  = 'BOOK MTH'                                        

000190          REPO_MTH  = 'REPO MTH'                                        

000191          PRICUSNM  = 'CUSTOMER NAME' ;                                 

000192                                                                        

000193 * CLIENT 84 - MERRILL SERVICES BY CENTERONE ;                          

000194                                                                        

000195 PROC PRINT DATA=WORKIN1 LABEL;                                         

000196  TITLE  "1099A &SYSDATE9 AT &SYSTIME " ;                               

000197  TITLE2 "                    MERRILL SERVICES BY CENTERONE" ;          

000198  TITLE3 "           BUSINESS ACCOUNTS REPOSSESSED LAST YEAR &LASTYEAR" ;

000199    WHERE CLIENT = '084';                                               

000200    BY CLIENT ;                                                         

000201    ID LNTYP  ;                                                         

000202    VAR ACTSTRING BOOK_MTH REPO_MTH PRICUSNM ;                          

000203    LABEL LNTYP     = 'CO CD'                                           

000204          ACTSTRING = 'LOAN NUMBER'                                     

000205          BOOK_MTH  = 'BOOK MTH'                                        

000206          REPO_MTH  = 'REPO MTH'                                        

000207          PRICUSNM  = 'CUSTOMER NAME' ;                                

000208                                                                        

000209                                                                        

000210 * CLIENT 85 - MITSUBISHI MOTORS CREDIT OF AMERICA ;                    

000211                                                                        

000212 PROC PRINT DATA=WORKIN1 LABEL;                                         

000213  TITLE  "1099A &SYSDATE9 AT &SYSTIME " ;                               

000214  TITLE2 "              MITSUBISHI MOTORS CREDIT OF AMERICA" ;          

000215  TITLE3 "           BUSINESS ACCOUNTS REPOSSESSED LAST YEAR &LASTYEAR" ;

000216    WHERE CLIENT = '085';                                               

000217    BY CLIENT ;                                                         

000218    ID LNTYP  ;                                                         

000219    VAR ACTSTRING BOOK_MTH REPO_MTH PRICUSNM ;                          

000220    LABEL LNTYP     = 'CO CD'                                           

000221          ACTSTRING = 'LOAN NBR'                                        

000222          BOOK_MTH  = 'BOOK MTH'                                        

000223          REPO_MTH  = 'REPO MTH'                                        

000224          PRICUSNM  = 'CUSTOMER NAME' ;                                 

000225                                                                        

000226 * CLIENT 600 - CENTER ONE FINANCIAL SERVICES LLC ;                     

000227                                                                       

000228 PROC PRINT DATA=WORKIN1 LABEL;                                         

000229  TITLE  "1099A &SYSDATE9 AT &SYSTIME " ;                               

000230  TITLE2 "              CENTERONE FINANCIAL SERVICES LLC " ;            

000231  TITLE3 "           BUSINESS ACCOUNTS REPOSSESSED LAST YEAR &LASTYEAR" ;

000232    WHERE CLIENT = '600';                                               

000233    BY CLIENT ;                                                         

000234    ID LNTYP  ;                                                         

000235    VAR ACTSTRING BOOK_MTH REPO_MTH PRICUSNM ;                          

000236    LABEL LNTYP     = 'CO CD'                                           

000237          BOOK_MTH  = 'BOOK MTH'                                        

000238          ACTSTRING = 'LOAN NBR'                                        

000239          REPO_MTH  = 'REPO MTH'                                        

000240          PRICUSNM  = 'CUSTOMER NAME' ;                                 

000241                                                                        

000242  COMMENT ******************************************************* ;     

000243  COMMENT * CREATE EXCEL SPREADSHEET FILE FROM SAS WORKIN1 FILE   ;     

000244  COMMENT ******************************************************* ;     

000245                                                                        

000246 DATA  _NULL_   ;                                                       

000247  FILE OUTFILE1 DSD ;                                                  

000248                                                                        

000249 IF _N_=1 THEN PUT 'LN_TYP,LOAN_NBR,BOOK_MTH,REPO_MTH,CUST_NAME,AADR_LIN_

000250 1,AADR_LIN_2,CCTY,SSTCOD,ZZIP_COD' ;                                   

000251                                                                        

000252 SET WORKIN1 ;                                                          

000253                                                                        

000254 PUT (LNTYP ACTSTRING BOOK_MTH REPO_MTH PRICUSNM PRIADDR1 PRIADDR2 PRICIT

000255 Y PRISTATE PRIZIP) (Smiley Happy ;                                               

000256                                                                        

000257                                                                        

000258 COMMENT * THE (Smiley Happy MODIFIER COMPRESSES THE DATA REMOVING TRAILING SPACES

000259                                                                        

****** **************************** Bottom of Data ****************************

Super User
Super User
Posts: 7,079

Re: CSV file to mainframe

In reality you should be able to manipulate the data with SAS statements without writing it out and reading it back.  Just like you are creating ACTSTRING at line number 78. 

For example if your dates are stored as numbers that look like dates ( For example today's date in year month date order would be the number 20,140,218) then you can convert them to actual SAS date values and use date related functions and formats to work on them. 

infile ... ;

input ... OGLSDBDT PD5. .... ;

OGLSDBDT = input(put(OGLSDBDT,8.),yymmdd8.);

format OGLSDBDT yymms7. ;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2221 views
  • 0 likes
  • 3 in conversation