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 ****************************
... View more