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.
Good morning, @Reeza. Wondering if you will have a minute to take a look at this for me today?
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));
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.
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
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();
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, 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:
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
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.
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!
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.