BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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.

elwayfan446_3-1605563848965.png

I have another dataset that has the loan history.

 

elwayfan446_1-1605563578485.png

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

I would recommend backing up and starting straight from your reporting code and follow the tutorial. 

The code posted in the forum thus far was sticking with your approach it was not following the tutorial.

 

Taking your code and refactoring it use the tutorial, this should work, assuming your reporting code is correct. 

 


%macro generate_report(seqNo = ,  loanID = , orig_lender = );

		ods pdf file="/sae/aal_labapm/01/mortgage/msracq/DEVELOPMENT/&orig_lender._history_epo_&loanID..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 = &seqNo.;
		    by pnc_loan_number;

                run;

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

		run;

	   ods pdf close;

%mend generate_report;


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;

*can be a data _null_ step instead but I leave it as a dataset for debugging initially;
data execute_reports;
set mapSeq;

str = catt('%generate_report(seqNo=', 
            seqNo,
            ' ,loanID = ',
             loanID,
             ' ,orig_lender = ', 
             orig_lender,
             ');'
           );
*Uncomment this line once you are sure the string is being generated correctly;
*call execute(str);
run;

View solution in original post

33 REPLIES 33
elwayfan446
Barite | Level 11

Bumping this post hoping someone will see and can help.

Reeza
Super User
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
Barite | Level 11

@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
Super User

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
Barite | Level 11

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
Barite | Level 11

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
PROC Star

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
Barite | Level 11

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
Barite | Level 11

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
Super User
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
Barite | Level 11
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
Super User
No, it is not difficult, the code is above once you create the format.
elwayfan446
Barite | Level 11
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
Barite | Level 11

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 33 replies
  • 1113 views
  • 0 likes
  • 4 in conversation