How to interleave two proc print reports in sas?

[Sheeba]

How to interleave the results from two proc print procedures. I would like to print first by group values from the first proc print followed by the same by group of the second proc print, then the next by group from the first proc print and then the second by group from the second proc print.

it will be great if you can help me out.

Regards,

Sheeba

A     
     
Identifier AMT1  AMT2 Interest  Totale  date  Sufx
0011342  100 50  1 150 07/05/2019 CA
0011342  200 100  2 400 07/05/2019 AZ
0011343  300 490  3 1770 07/05/2019 CA
0011345  400 653.3333 4 3013.33307/05/2019 az
0011346  500 873.3333 5 4866.66707/05/2019 CA
0011347  600 1093.333 6 7160 07/05/2019 AZ
0011348  700 1313.333 6 8580 07/05/2019

pmt
Identifier type checknumber amount
0011342  chk 1234  450
0011343  chk 5678  790

/*code for creating first report*/

proc summary data=a nway;
class identifier date sufx;
var amt1 amt2 total;
output out=b(drop=_type_ _freq_) sum=;
run;
 
ods listing close;
ods pdf file='test.pdf' newfile=bygroup;
 
proc print data=b noobs;
by identifier ;
id identifier ;
var date sufx amt1 amt2 total;
run;

proc print data=pmt noobs;
by identifier ;
id identifier ;
var type checknumber amt;
run;
 
ods pdf close;
ods listing;
 How do I add the code for creating a report out of PMT so that I can attach the information for each identifier at the end of each group of first report?

editing to add the output

INPUT

Identifier AMT1  AMT2 Interest  Totale  date  Sufx

0011342  100 50  1 150 07/05/2019 CA

0011342  200 100  2 400 07/05/2019 AZ

0011343  300 490  3 1770 07/05/2019 CA

0011345  400 653.3333 4 3013.33307/05/2019 az

0011346  500 873.3333 5 4866.66707/05/2019 CA

0011347  600 1093.333 6 7160 07/05/2019 AZ

0011348  700 1313.333 6 8580 07/05/2019

pmt

Identifier type checknumber amount

0011342  chk 1234  450

0011343  chk 5678  790

OUTPUT

PDF1
Identifier	date	suffis	AMT1	AMT2	interest	total
0011342	7/5/2019	CA	100	50	1	150
	7/5/2019	AZ	200	100	2	400
identifier	type	checknumber	amount
0011342	chk	1234	450
PDF2
Identifier	date	suffis	AMT1	AMT2	interest	total
0011343	7/5/2019	CA	300	490	3	1770
identifier	type	checknumber	amount
0011342	chk	5678	790
PDF3
Identifier	date	suffis	AMT1	AMT2	interest	total
0011345	7/5/2019	AZ	400	653.333	4	3013.33
PDF4
Identifier	date	suffis	AMT1	AMT2	interest	total
0011346	7/5/2019	CA	500	873.33	5	4866.66
PDF5
Identifier	date	suffis	AMT1	AMT2	interest	total
0011347	7/5/2019	AZ	600	1093.33	6	7160
PDF6
Identifier	date	suffis	AMT1	AMT2	interest	total
0011346	7/5/2019	CA	700	1313.33	6	8580

[Sheeba]

How to interleave the results from two proc print procedures. I would like to print first by group values from the first proc print followed by the same by group of the second proc print, then the next by group from the first proc print and then the second by group from the second proc print.

it will be great if you can help me out.

Regards,

Sheeba

A     
     
Identifier AMT1  AMT2 Interest  Totale  date  Sufx
0011342  100 50  1 150 07/05/2019 CA
0011342  200 100  2 400 07/05/2019 AZ
0011343  300 490  3 1770 07/05/2019 CA
0011345  400 653.3333 4 3013.33307/05/2019 az
0011346  500 873.3333 5 4866.66707/05/2019 CA
0011347  600 1093.333 6 7160 07/05/2019 AZ
0011348  700 1313.333 6 8580 07/05/2019

pmt
Identifier type checknumber amount
0011342  chk 1234  450
0011343  chk 5678  790

/*code for creating first report*/

proc summary data=a nway;
class identifier date sufx;
var amt1 amt2 total;
output out=b(drop=_type_ _freq_) sum=;
run;
 
ods listing close;
ods pdf file='test.pdf' newfile=bygroup;
 
proc print data=b noobs;
by identifier ;
id identifier ;
var date sufx amt1 amt2 total;
run;

proc print data=pmt noobs;
by identifier ;
id identifier ;
var type checknumber amt;
run;
 
ods pdf close;
ods listing;
 How do I add the code for creating a report out of PMT so that I can attach the information for each identifier at the end of each group of first report?

[SASKiwi]

Each PROC PRINT is independent of each other and runs one after the other so what you are trying to do is impossible with that procedure.

PROC REPORT might get you closer to what you want. You will need to post some sample data with your code, plus a mock-up of what you want your report to look like.

[Sheeba]

Hi SASKiwi,

Thank you so much for the reply. Yes I was unable to get the results through proc print even though I added a proc print to my code after ods(included in the post).

I have added the expected output.

Regards,

Sheeba

[koyelghosh]

@Sheeba I am sure @SASKiwi is totally correct. When an expert is saying so then most likely a procedure does not exist and/or there are no options/statements in proc print to achieve it. Thus very hesitantly, I would like to use a Macro (against warnings and ignoring finger burns). I am sure there is a more elegant way to achieve this. Let my code be a springboard to expedite the arrival of that elegant code (in waiting).

Below is the data (from your post). I have called it HAVE_1 and HAVE_2. You can ignore this block since you already have the data.

DATA HAVE_1;
	INPUT Identifier AMT1 AMT2 Interest Totale date ddmmyy10. Sufx $3.;
	FORMAT date ddmmyy10.;
	DATALINES;
0011342 100 50 1 150 07/05/2019 CA
0011342 200 100 2 400 07/05/2019 AZ
0011343 300 490 3 1770 07/05/2019 CA
0011345 400 653.3333 4 3013.333 07/05/2019 az
0011346 500 873.3333 5 4866.667 07/05/2019 CA
0011347 600 1093.333 6 7160 07/05/2019 AZ
0011348 700 1313.333 6 8580 07/05/2019
;
RUN;

DATA HAVE_2;
	INPUT Identifier type $3. checknumber amount;
	DATALINES;
0011342 chk 1234 450
0011343 chk 5678 790
;
RUN;

.... and rather begin here.

PROC SQL;
	CREATE TABLE Identfs_Table1 AS SELECT DISTINCT(Identifier) AS Identfs_1 FROM 
		HAVE_1;
	CREATE TABLE Identfs_Table2 AS SELECT DISTINCT(Identifier) AS Identfs_2 FROM 
		HAVE_2;
	CREATE TABLE All_Identfs AS SELECT Identfs_1 AS MergedIdentifier FROM 
		Identfs_Table1 UNION SELECT Identfs_2 FROM Identfs_Table2;
QUIT;

%MACRO PrintOneByOne();
	PROC SQL NOPRINT;
		SELECT COUNT(*) INTO :CountOfIdentfs FROM All_Identfs;
	QUIT;

	%DO I=1 %TO &CountOfIdentfs;

		DATA _NULL_;
			SET All_Identfs (FIRSTOBS=&I OBS=&I);
			CALL SYMPUT('Identifier_Queried', MergedIdentifier);
		RUN;

		DATA Table_1;
			SET HAVE_1;
			IF Identifier=&Identifier_Queried THEN
				OUTPUT;
		RUN;
		
		PROC PRINT DATA=TABLE_1 noobs;
			ID identifier;
			VAR date sufx amt1 amt2 totale;
			TITLE "Have_1 Table results";
		run;
		
		DATA Table_2;
			SET HAVE_2;
			IF Identifier=&Identifier_Queried THEN
				OUTPUT;
		RUN;
		PROC PRINT DATA=TABLE_2 noobs;
			ID identifier;
			VAR type checknumber amount;
			TITLE "Have_2 Table results";
		run;
	%END;
%MEND;

%PrintOneByOne();

It is getting the job done, except for two things. 

(1) The code is long and windy. Needs optimization or totally abandoning the macro in favor of a smart Procedure!!

(2) I could not get rid of the lines between each PROC output.

The output is as below.

Output

Let me know if this was anywhere close to what you wanted. Just curious about how well I read the problem statement.

Thanks.

[SASKiwi]

@koyelghosh - Nice solution. Yes of course you can split your data into chunks and run a series of PROC PRINTs. I was visualizing that the results the OP wanted had to be in one contiguous table, which would require just one PROC run once. I'm waiting for the OP to show us more clearly what he wants.

[Patrick]

I've never done it myself but wouldn't Proc Document together with ODS Document allow to capture and rearrange output objects for printing?

I believe each by group in a Proc creates its own output object (each table is an object) so using ODS wouldn't it be possible to first only capture and re-arrange the objects by by-group from multiple Procs and only then print them?

Here a white paper with details how this could work: https://support.sas.com/resources/papers/sgf09/318-2009.pdf 

[Sheeba]

Hi Patrick,

Thank you so much for the reply.

I will go through the link and will try the proc document. I have never used it before. I will let you know how it goes.

Regards,

Sheeba

[Sheeba]

Hi Koyelgosh,

Thank you so much for the detailed reply with code.

Yes, this is exactly what I was looking for . I will try this code.

ALso I was trying to send each block into separate pdf's. So here in total I am expecting to generate 6 PDF's .

one PDF for holding the results for 0011342 . Another PDF for holding the results for 0011343. Another one for 001135, Another PDF for 0011346, Pdf for 0011347 and PDF for 0011348.

Regards,

Sheeba Swaminathan

[Tom]

Write a macro that takes as an input the id value to be printed.  Have it generate the ODS and PROC statements.

%macro print_one(id);
ods pdf file="/directoryname/report_&id..pdf";
proc print data=table1 ;
  where id=&id;
run;
proc print data=table2;
  where id=&id;
run;
ods pdf close;
%mend print_one;

Then call the macro once for each id value.

data _null_;
  set table1(keep=id) table2(keep-id);
  by id;
  if first.id;
  call execute(cats('%nrstr(%print_one)(',id,')'));
run;

If you ID variable is character you will need to add quotes around the value in your WHERE statements.  

where id="&id";

[Sheeba]

Hi Tom,

Thank you so much for the reply.

I will try this code and will let you know how it goes.

Thanks,

Regards,

Sheeba

[koyelghosh]

@Sheeba .. I mentioned you in a thread that I started here. I thought I will post here for your reference.

[Reeza]

In general, for this type of problem, especially because you need a unique file and you may want to control the file name you need to have a macro code. My general approach to solving these types of problems is outlined here:

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Always start out with working code for your base case and then turn it into a macro. 

[Sheeba]

Hi Reeza,

Thanks a lot for the reply.

The link is really helpful.

Regards,

Sheeba

[koyelghosh]

@Tom much cleaner, much compact and much elegant ... Experience shining in code .. Thank you .. I had requested for exactly  this sort of reply