topic Re: CSV file to mainframe in SAS Programming

CSV file to mainframe

bolleau — Mon, 17 Feb 2014 23:24:44 GMT

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

Re: CSV file to mainframe

Tom — Tue, 18 Feb 2014 00:21:10 GMT

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) (:) ;

run;

Re: CSV file to mainframe

overmar — Tue, 18 Feb 2014 00:46:21 GMT

Tom,

I have seen you put (:) 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?

Re: CSV file to mainframe

Tom — Tue, 18 Feb 2014 02:44:18 GMT

: 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) (:) ; 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.

Re: CSV file to mainframe

bolleau — Tue, 18 Feb 2014 21:38:28 GMT

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) (:) ;

000256

000257

000258 COMMENT * THE (:) MODIFIER COMPRESSES THE DATA REMOVING TRAILING SPACES

000259

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

Re: CSV file to mainframe

Tom — Tue, 18 Feb 2014 22:08:50 GMT

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. ;