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

[elwayfan446]

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!

[elwayfan446]

Bumping this post hoping someone will see and can help.

[Reeza]

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

[elwayfan446]

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

[ballardw]

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

[elwayfan446]

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;

[elwayfan446]

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.

[SASKiwi]

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=p0r...

[elwayfan446]

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.

[elwayfan446]

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?

[Reeza]

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"

[elwayfan446]

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?

[Reeza]

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

[elwayfan446]

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

[elwayfan446]

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.