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
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;
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;
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?
: 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.
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 ****************************
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. ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.