topic Re: Creating a Loan History and Exporting it to PDF from SAS Enterprise Guide 8.1 in SAS Enterprise Guide

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

elwayfan446 — Mon, 16 Nov 2020 22:26:35 GMT

Hi everyone.

I am trying to create a loan history from different SAS datasets using Enterprise Guide. I have one dataset that has the borrower name and address.

I have another dataset that has the loan history.

I am trying to figure out a way to so somewhat of a merge where the address record has the appearance of an address on a letter and the history records pull in just as they are in this dataset on the bottom section of the page. I want to export an individual PDF file for each ID.

So....

John Doe

123 Somewhere Street

Apt B

Somewhere, CA 11111

Then pull in the records from the second dataset here below the address.

Any help with this would be greatly appreciated!

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

elwayfan446 — Tue, 17 Nov 2020 14:11:32 GMT

Bumping this post hoping someone will see and can help.

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

elwayfan446 — Tue, 17 Nov 2020 16:40:37 GMT

@Reeza @ballardw @Kurt_Bremser

Do any of you have any suggestions? I am desperate to figure out the best way to do this for a project that I am told needed to be done ASAP.

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

ballardw — Tue, 17 Nov 2020 17:05:15 GMT

Data.

We can't code from pictures and having to make up too much data really eats into the volunteer time we have.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Might give someone enough to work on.

And actual example of the output document may help as well.

Once upon a time SAS supported Forms and had sort of mail merge functionality with those, which I think comes close to what you want.

You might be ahead to export the data from SAS and use a mail merge. Maybe.

I don't work in EG so any solution I come up with would be code and when you have TWO data sets synchronizing the two for this sort of bit can be tricky because you need to use two different report generating steps with a common WHERE to use the ID.

I might try transposing the address information and using a data step with a File Print ODS to get that output and any of Proc Print or Report with an appropriate Where would display the history information.

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

elwayfan446 — Tue, 17 Nov 2020 22:41:59 GMT

Thanks. Yes, I have posted code many times. I just thought I would post the end result of what I wanted to pull together to see if there was a quick way to do it.

I found this old post and the code mostly works for what I want. My biggest issue is that I am using loan numbers for the min and max variables to loop by. Even though there are only 2 loans (the actual min and max) enterprise guide thinks that it is exporting a PDF for every number between the 2 ID's. It makes the results take forever to generate. The crazy thing is, it only creates the 2 pdf files I want (for the min and the max loan number). If I had 3 loans, I would want a file generated for each.

Here is the code:

options orientation=landscape nodate nonumber;
%macro reporting;
proc sql;
	select min(loan_number)format=best10.,max(loan_number) format=best10. into :firstgrp, :lastgrp 
	from work.address;
quit;

	%do i = &firstgrp. %to &lastgrp.;
	    ods pdf file="/.../history_epo_&i..pdf" style=htmlblue startpage=never;

		proc report data=work.address nowd;
		    where loan_number = &i.;
		    /*by loan_number;*/
          run;

		proc report data=work.paytran nowd;
		    where loan_number = &i.;
		   /*by loan_number;*/
		run;

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

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

elwayfan446 — Tue, 17 Nov 2020 22:49:15 GMT

If I use the ods results off option it keeps it from taking so long in EG. I still wonder why it is trying to run it for all numbers between (for example) 1 and 10 if there is ONLY a 1 and a 10.

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

SASKiwi — Wed, 18 Nov 2020 00:04:31 GMT

This statement - %do i = &firstgrp. %to &lastgrp.; - repeats the logic in your loop starting at the first group, then adding 1 to i for each loop until it reaches last group. That's why it is doing all number between 1 and 10.

Reading the documentation will improve your understanding: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=mcrolref&docsetTarget=p0ri72c3ud2fdtn1qzs2q9vvdiwk.htm&locale=en

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

elwayfan446 — Wed, 18 Nov 2020 00:18:42 GMT

Thanks @SASKiwi. That makes sense. Now I need to figure out how to make the loop only look at 1 & 10, or 1, 10, and 18 if need be. Basically I have a list of loan numbers... I only want results for the actual numbers. I can convert the loan numbers to character but I haven't figured out how I would be able to do loop through with a character variable.

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

Reeza — Wed, 18 Nov 2020 16:20:42 GMT

Data _null_ or ODS Report Writer offers capabilities. Personally, Data _null_ would work. There's an old paper from Cynthia Zender, called Creating Complex Reports or if you check lexjansen.com and search "form letters" you'll get the information you need. I would suggest you consider a Word/Excel mail merge though as that would be very trivial to implement (half a day) whereas formatting and layout via code will definitely take time.

https://www.lexjansen.com/nesug/nesug05/cc/ccx4.pdf
https://www.mwsug.org/proceedings/2009/stats/MWSUG-2009-D09.pdf
https://www.lexjansen.com/wuss/2009/app/APP-OConnor.pdf

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

elwayfan446 — Wed, 18 Nov 2020 16:31:42 GMT

I figured out how to do this part by using the retain statement to create a sequence number for ever loan number in my dataset. This insures that the same sequence number is tied to each loan.

data work.address;
	set work.address;
	by loan_number;
retain seq_id 0;
if first.loan_number then seq_id=seq_id + 1;
run;

Once that is done, then I can select the max and min into the variables and it prints the pdf files correctly (without treating the loan numbers as the min and max and printing all of the loan numbers that would be in between).

Here is the new code that works.

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;

	%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_labapm/01/mortgage/msracq/DEVELOPMENT/logo.jpg']}";

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

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

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

However, now that the variable &i is the seq_id, that is what is printing as part of the .pdf file name instead of the loan number. Is there a way to print the loan number that is tied to that particular seq_id from the dataset in the .pdf file name?

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

Reeza — Wed, 18 Nov 2020 16:45:18 GMT

Use a format to map the number to each name and then apply that format using PUTC in the ODS PDF statement.

"/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/%sysfunc(putc(history_epo_&i, yourFmt).pdf"

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

elwayfan446 — Wed, 18 Nov 2020 16:48:39 GMT

Ah, cool. I have not used formats believe it or not. I will look into it but is it difficult to use one to map to the name?

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

Reeza — Wed, 18 Nov 2020 16:58:05 GMT

No, it is not difficult, the code is above once you create the format.

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

elwayfan446 — Wed, 18 Nov 2020 17:20:39 GMT

Sounds good. I will give it a shot. Once I have it I will come back and mark this thread answered. I appreciate everyone.

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

elwayfan446 — Tue, 01 Dec 2020 13:00:54 GMT

Good morning @Reeza. Could you help me with creating a format to do this? I have tried to follow along with the SAS documentation and I just can't figure it out.

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

Reeza — Tue, 01 Dec 2020 17:04:52 GMT

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

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

elwayfan446 — Mon, 07 Dec 2020 16:00:50 GMT

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.

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

elwayfan446 — Tue, 08 Dec 2020 14:08:24 GMT

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

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

Reeza — Tue, 08 Dec 2020 17:09:22 GMT

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

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

elwayfan446 — Tue, 08 Dec 2020 20:30:10 GMT

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.