Creating a Loan History and Exporting it to PDF from SAS Enterprise Guide 8.1

[Reeza]

Post what you tried and I'll be happy to help from there.

[elwayfan446]

Thanks @Reeza for getting back to me.  I got tied up last week with end of month work and didn't have a chance to post until now.  Here is what I have so far.  This includes my entire script.  The section I am trying to create a format with is in between comments.

options orientation=landscape nodate nonumber;
ods results off;

%macro reporting;
proc sql;
	select min(seq_id)format=best10.,max(seq_id) format=best10. into :firstgrp, :lastgrp 
	from work.address a1;
quit;

/****TRYING TO CREATE A FORMAT SO I CAN INSERT LOAN NUMBER INTO PDF FILE NAME****/

data cntlin(keep= fmtname type hlo start label);
	retain fmtname 'loannum' type 'C';
	set work.address end= lastrec;
	start = seq_id; label = pnc_loan_number;
	output;

	if lastrec then do;
	  hlo = '0'; label = 'loan_number';
	  output;
end;
run;

/********************************************************/



	%do i = &firstgrp. %to &lastgrp.;
	     ods pdf file="/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/history_epo_&i..pdf" style=htmlblue startpage=never bookmarklist=hide;
		title "^S={font_size=14pt} LOAN HISTORY";
		ods escapechar='^';
		ods pdf text="^{style[just=left preimage='/sae/aal_labxxx/01/mortgage/msracq/DEVELOPMENT/logo.jpg']}";

		proc report data=work.address nowd;
		    where seq_id = &i.;
		    by pnc_loan_number;

          run;

		proc report data=work.paytran nowd;
		    where seq_id = &i.;
		   by loan_number;

		run;

	   ods pdf close;
	%end;
%mend reporting;
%reporting;

Here are the results (numbers blurred a little for privacy).  I just can't put it all together.  I may be making this harder than it should be.

[elwayfan446]

Good morning, @Reeza.  Wondering if you will have a minute to take a look at this for me today?

[Reeza]

1. Add a PROC FORMAT

data yourCustomFormat (keep= fmtname type hlo start label);
	retain fmtname 'loannum' type 'C';
	set work.address end= lastrec;
	start = seq_id; label = pnc_loan_number;
	output;

	if lastrec then do;
	  hlo = '0'; label = 'loan_number';
	  output;
       end;
run;

proc format cntlin =yourCustomFormat;
run;

2. Use your format

ods pdf file="/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/history_epo_%sysfunc(putn(&i, loannum)).pdf"

PS. Test your format, assuming sequence values of 1 and 8 are valid:

%put %sysfunc(putn(1, loannum));
%put %sysfunc(putn(8, loannum));

[elwayfan446]

I am a little closer but I am running into a new issue.

First, I had to comment out the following code because it was creating a record that was throwing an error due to multiple seq_id's that were the same.  No problems there.

Here is the updated code:

data namefmt(keep= fmtname type /*hlo*/ start label);
	retain fmtname 'loannum' type 'C';
	set work.address end= lastrec;
	start = seq_id; label = pnc_loan_number;
	output;

	/*if lastrec then do;
	  hlo = '0'; label = 'loan_number';
	  output;
	end;*/
run;

proc format cntlin =namefmt;
run;

Here is the output of the format:

When I run the rest of the code, I am getting a file name that looks like these instead of including the loan number.

To test, I tried to run this but it isn't giving me a result in the log, just printing the function.

%put %sysfunc(putn(1, loannum));

*** Edit to add that the loan number (what is showing in the label field of the format results above) is what I am trying to get into the pdf file name.

[Reeza]

Try PUTC() since you have a character format defined, but you have a number.... I think your format should be numeric not a character format.

Without seeing the actual code you ran I have no idea of what the issue may be.

[elwayfan446]

The loan number is actually a character field (it could have char characters included in the "number").

Here is the latest version of the code I am trying to run:

options orientation=landscape nodate nonumber;
ods results off;

%macro reporting;
proc sql;
	select min(seq_id)format=best10.,max(seq_id) format=best10. into :firstgrp, :lastgrp 
	from work.address a1;
quit;

/****TRYING TO CREATE A FORMAT SO I CAN INSERT LOAN NUMBER INTO PDF FILE NAME****/

data namefmt(keep= fmtname type /*hlo*/ start label);
	retain fmtname 'loannum' type 'C';
	set work.address end= lastrec;
	start = seq_id; label = pnc_loan_number;
	output;

	/*if lastrec then do;
	  hlo = '0'; label = 'loan_number';
	  output;
	end;*/
run;

proc format cntlin =namefmt;
run;

/********************************************************/

	%do i = &firstgrp. %to &lastgrp.;
	     ods pdf file="/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/history_epo_%sysfunc(putn(&i, loannum)).pdf" style=htmlblue startpage=never bookmarklist=hide;
		title "^S={font_size=14pt} LOAN HISTORY";
		ods escapechar='^';
		ods pdf text="^{style[just=left preimage='/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/logo.jpg']}";

		proc report data=work.address nowd;
		    where seq_id = &i.;
		    by pnc_loan_number;

          run;

		proc report data=work.paytran nowd;
		    where seq_id = &i.;
		   by pnc_loan_number;

		run;

	   ods pdf close;
	%end;
%mend reporting;
%reporting;

So, as mentioned above, my goal is to have the pnc_loan_number in the pdf file name.  For example: history_epo_1610002.pdf

[Reeza]

Log? Did it run correctly or were there errors? Logistically, it looks correct.

[Reeza]

FYI - you could avoid all of this by using the ID number and not looping but using CALL EXECUTE as illustrated here.
One of the reasons I recommend this approach is it's much easier to debug and maintain in the long run.
https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[Reeza]

Here's a fully worked and tested solution. I'll let you find the bugs in your own logic via comparison.

*create example data;

data cars;
	set sashelp.cars;
run;

proc sort data=sashelp.cars out=cars;
	by make;
run;

*add sequence numbers to mimic your structure;

data cars;
	set cars;
	by make;
	retain SeqNo;

	if first.make then
		seqNo + 1;
run;

*get max of seq for loop;

proc sql noprint;
	select max(seqNo) into :NumObs from cars;
quit;

*create format to map numbers to make;

proc sql;
	create table mapSeq2Make as select distinct seqNo as Start, trim(Make) as 
		Label, 'make_fmt' as fmtName, 'N' as type from cars;
quit;

*create format;

proc format cntlin=mapSeq2Make;
run;

*test mapping;
%put %sysfunc(putn(1, make_fmt));
*macro to create reports;

%macro report_make();
	%do i=1 %to &numObs;
		ods html file="/home/fkhurshed/Demo1/MileageReport_%sysfunc(putn(&i, make_fmt)).html"  /*1*/
		gpath='/home/fkhurshed/Demo1/' style=meadow;
		ods graphics / imagemap=on;
		title "Report on Mileage for %sysfunc(putn(&i, make_fmt))";

		/*2*/
		title2 'Summary Statistics';

		proc means data=sashelp.cars (where=(make="%sysfunc(putn(&i, make_fmt))")) 
				/*3*/
				N Mean Median P5 P95 MAXDEC=2;
			class type;
			ways 0 1;
			var mpg_city mpg_highway;
		run;

		title 'City vs Highway Mileage';

		proc sgplot data=sashelp.cars (where=(make="%sysfunc(putn(&i, make_fmt))")) 
				/*4*/;
			scatter x=mpg_city y=mpg_highway / group=type;
		run;

		ods html close;
	%end;
%mend report_make;

*execute macro;
%report_make();

[elwayfan446]

Thanks so much for the help Reeza.  I will take a look at this code in the morning and see what I can do and learn!

[Reeza]

For one you're using PUTN but your format type is still set to C, not N.

[elwayfan446]

Reeza, thanks for the new code, that is much easier and I can tell it will be easier to maintain.

So a couple of things... it is weird.  If I change the loan format to a C it prints with a blank.  If I change it back to an N, it prints.  It is a char filed in my dataset so I am not sure why that would be the case.

As N:

As C:

Also, yesterday afternoon my boss came to me and wants me to add the lender name in the title as well.  When I tried to create a new format for that, I get a big space in the file for it as well instead of the lender name.  Here is my modified code that you provided me yesterday:

proc sort data=address out=address;
	by pnc_loan_number;
run;

proc sort data=paytran out=paytran;
	by pnc_loan_number;
run;



*add sequence numbers to mimic your structure;

data address;
	set address;
	by pnc_loan_number;
	retain SeqNo;

	if first.pnc_loan_number then
		seqNo + 1;
run;

data paytran;
	set paytran;
	by pnc_loan_number;
	retain SeqNo;

	if first.pnc_loan_number then
		seqNo + 1;
run;

*get max of seq for loop;

proc sql noprint;
	select max(seqNo) into :NumObsadd from address;
quit;

*create format to map numbers to loan and lender;

proc sql;
	create table mapSeq2loan as select distinct seqNo as Start, trim(pnc_loan_number) as 
		Label, 'loan_fmt' as fmtName, 'N' as type from address;
quit;

proc sql;
	create table mapSeq2lender as select distinct seqNo as Start, trim(orig_lender) as 
		Label, 'lender_fmt' as fmtName, 'C' as type from address;
quit;
*create format;

proc format cntlin=mapSeq2loan;
run;

proc format cntlin=mapSeq2lender;
run;

*test mapping;
%put %sysfunc(putn(1, loan_fmt));
%put %sysfunc(putc(1, lender_fmt));
*macro to create reports;

%macro report_make();
	%do i=1 %to &numObsadd;
		ods pdf file="/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/%sysfunc(putc(&i, lender_fmt))_history_epo_%sysfunc(putn(&i, loan_fmt)).pdf" style=htmlblue startpage=never bookmarklist=hide;
		title "^S={font_size=14pt} LOAN HISTORY";
		ods escapechar='^';
		ods pdf text="^{style[just=left preimage='/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/logo.jpg']}";

		proc report data=work.address nowd;
		    where SeqNo = &i.;
		    by pnc_loan_number;

          run;

		proc report data=work.paytran nowd;
		    where SeqNo = &i.;
		   by pnc_loan_number;

		run;

	   ods pdf close;
	%end;
%mend report_make;
%report_make();

The results:

[Reeza]

It's the way the formats are being applied then...if you can, switching the other method I've been suggesting would make this all go away. Make it a self contained macro you call via CALL EXECUTE and pass the exact parameters you'd like over.

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[elwayfan446]

Thanks Reeza. 

I thought I was working toward the macro function in your tutorial but I must not be connecting the dots.  Let me break it down a little better on what I am trying to do and hopefully you can help me see what I am missing.

I have a dataset that I created with this code.

proc sql;
	create table mapSeq as 
	select distinct 
		seqNo as Start
		, trim(loan_number) as loan_number
		, trim(lender) as orig_lender 
	from address;
quit;

This is the resulting dataset with the unique key being the start column.

I need to be able to create a .pdf file for each record in the dataset that includes the loan_number and orig_lender in the file name of the .pdf  This is the code I have so far.  I have used place holders in the file name where I need each of these to appear.

%macro report_make();
	%do i=1 %to &numObsadd;
		ods pdf file="/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/<orig_lender>_history_epo_<loan_number>.pdf" style=htmlblue startpage=never bookmarklist=hide;
		title "^S={font_size=14pt} LOAN HISTORY";
		ods escapechar='^';
		ods pdf text="^{style[just=left preimage='/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/logo.jpg']}";

		proc report data=work.address nowd;
		    where SeqNo = &i.;
		    by pnc_loan_number;

          run;

		proc report data=work.paytran nowd;
		    where SeqNo = &i.;
		   by loan_number;

		run;

	   ods pdf close;
	%end;
%mend report_make;
%report_make();

Looking at your tutorial, it isn't clear to me how to create a variable or if I need to create one to insert the loan_number and orig_lender by start.  I am probably making this harder than it needs to be.  I am sorry for all of the back and forth but your help has been greatly appreciated.