BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
_Manhattan
Obsidian | Level 7

Hey guys,

 

even though there are some very helpful ressources on ods layout, which I used in the past, I do not manage to implement the ods layout/ods region statement successfully in my code. My example code is a slightly modified version of a code that @Oligolas helped me out with (https://communities.sas.com/t5/SAS-Programming/Using-conditional-logic-to-join-tables-inside-a-do-lo...). The desired result should look similar to the Screenshots down below. To summarize my question: How can I incorporate ODS layout within my do-loop to display two (or more) PROC REPORT tables side by side, instead of stacking them vertically?

proc datasets lib=work kill nolist;run;quit;

%let yourpath=C:\;


/*** Example Data ***/
data work.sample_data;
  input EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;
  datalines;
  10 20 30 40 50  1 2 3 2 3
  15 25 35 45 55  1 3 4 3 2
  5  10 15 20 25  2 2 3 3 2
  20 30 40 50 60  4 3 2 1 1
  25 35 45 55 65  1 1 1 2 3
  ;
run;

data work.input_data;
	length Info1 $9 Info2 $16;
	input Info1 $ Info2 $ Sort;
	infile datalines delimiter=",";
	datalines;
	Variable,Answer your Item,1
	AGHK50A,Item A,1
	AGHK50B,Item B,1
	AGHK50C,Item C,1
	Variable,Answer your Item,2
	BGKO28A,Item A,2
	BGKO28B,Item B,2
	Variable,Answer your Item,3
	EZGH25A,Item A,3
	EZGH25B,Item B,3
	EZGH25C,Item C,3
	EZGH25D,Item D,3
	EZGH25E,Item E,3
;

/* Using proc sql for a Sorting Variable which is used to iterate trough the do-loop */
proc sql noprint;
   select distinct Sort into :Sort separated by " "
   from work.input_data;
quit;

%put &Sort; 

%let varlistE = EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E;
%let varlistA = AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;

/* Mean Calculation */
proc means data=work.sample_data noprint;
  var &varlistE;
  output out=work.mean_ezgh;
run;

%macro meandata;
   %do i = 1 %to %sysfunc(countw(&varlistE));
   %let vmean = %scan(&varlistE, &i);
   
   data work.mean&vmean;
      set work.mean_ezgh (keep=&vmean);
   run;
   
   %end;
%mend meandata;
%meandata;

/* Freq calculation */
%macro freqdata;
   %do i = 1 %to %sysfunc(countw(&varlistA));
   %let vfreq = %scan(&varlista, &i);
   
   proc freq data=work.sample_data;
      tables &vfreq/ out=work.freq&vfreq;
   run;
   
   %end;
%mend freqdata;

%freqdata;


/*** Macro Loop ***/
ods _all_ close;
options leftmargin=.001in rightmargin=.001in topmargin=.001in bottommargin=.001in nodate nonumber;
%macro Testloop;
  %do i = 1 %to %sysfunc(countw(&Sort.));
    %let SortID = %scan(&sort, &i);
    %let VariableA = %scan(&varlistA, &i);
    %let VariableE = %scan(&varlistE, &i);
    
    ods pdf file="&yourpath.\LoopTest.pdf";
    ods pdf startpage=now;
    ods layout start width=19cm height=27cm;

/* Compute Variable Information for each Variable */
    data VariableInfo;
      set work.input_data;
      where Sort = &SortID.;
      drop Sort;
    run;
    
   %local Info1Items;
   %let Info1Items=;
   PROC SQL noprint;
      SELECT distinct Info1 INTO :Info1Items SEPARATED BY " "/*new*/
      FROM work.input_data
      WHERE Sort = &SortID.
      AND Info1 not in ('' 'Variable');
   QUIT;


/* Report Variable Information for each Variable */
    proc report data=VariableInfo noheader;
      column Info1 Info2;
    run;
    
    ods layout end;
    
   %MACRO PrinItemStats(Item=);
      %if %sysfunc(exist(freq&Item.)) %then %do; 
         /* Print Frequency Table */
        ods layout gridded advance=proc columns=2;
         proc report data=freq&Item.;
            column &Item. COUNT PERCENT;
            define &Item / display "&Item.";
            define COUNT / display "Absolut";
            define PERCENT / display "Percent";
         run;
         ods layout end;
      %end;

      %if %sysfunc(exist(mean&Item.)) %then %do; 
         /* Print Mean Table */
         ods layout gridded advance=proc columns=2;
         proc report data=mean&Item.;
            column &Item.; 
         run;
      ods layout end;
      %end;
   %MEND PrinItemStats;


   %local j currItem;
   %LET j=1;
   %LET currItem=%SCAN(&Info1Items.,&j.,%STR( ));
   %DO %WHILE(%LENGTH(&currItem.)>0);

      %PrinItemStats(Item=&currItem.);
      %LET j=%EVAL(&j.+1);
      %LET currItem=%SCAN(&Info1Items.,&j.,%STR( ));
   %END;

  %end;
  ods pdf close;
%mend Testloop;
%Testloop;

 

Sorting1.JPG

Sorting2.JPG

Sorting3.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Oligolas
Barite | Level 11

Hi,

 

I think you need to work with ods region.

There is a nice paper that may help you out here

 

As you see the alignment for the 'EZGH' Outputs is not left aligned since the 'second column' is here occupied by the 'Answer your item' column of the previous report.

You may want to merge all the means tables by hand prior to report them all at one on one table 


proc datasets lib=work nolist; delete sample_data input_data mean: VariableInfo freq: ;run ;quit;

%let yourpath=C:\;

ods listing;
/*** Example Data ***/
data work.sample_data;
  input EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;
  datalines;
  10 20 30 40 50  1 2 3 2 3
  15 25 35 45 55  1 3 4 3 2
  5  10 15 20 25  2 2 3 3 2
  20 30 40 50 60  4 3 2 1 1
  25 35 45 55 65  1 1 1 2 3
  ;
run;

data work.input_data;
   length Info1 $9 Info2 $16;
   input Info1 $ Info2 $ Sort;
   infile datalines delimiter=",";
   datalines;
   Variable,Answer your Item,1
   AGHK50A,Item A,1
   AGHK50B,Item B,1
   AGHK50C,Item C,1
   Variable,Answer your Item,2
   BGKO28A,Item A,2
   BGKO28B,Item B,2
   Variable,Answer your Item,3
   EZGH25A,Item A,3
   EZGH25B,Item B,3
   EZGH25C,Item C,3
   EZGH25D,Item D,3
   EZGH25E,Item E,3
;

/* Using proc sql for a Sorting Variable which is used to iterate trough the do-loop */
proc sql noprint;
   select distinct Sort into :Sort separated by " "
   from work.input_data;
quit;

%put &Sort; 

%let varlistE = EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E;
%let varlistA = AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;

/* Mean Calculation */
proc means data=work.sample_data noprint;
  var &varlistE;
  output out=work.mean_ezgh;
run;

%macro meandata;
   %do i = 1 %to %sysfunc(countw(&varlistE));
   %let vmean = %scan(&varlistE, &i);
   
   data work.mean&vmean;
      set work.mean_ezgh (keep=&vmean);
   run;
   
   %end;
%mend meandata;
%meandata;

/* Freq calculation */
%macro freqdata;
   %do i = 1 %to %sysfunc(countw(&varlistA));
   %let vfreq = %scan(&varlista, &i);
   
   proc freq data=work.sample_data;
      tables &vfreq/ out=work.freq&vfreq;
   run;
   
   %end;
%mend freqdata;

%freqdata;


/*** Macro Loop ***/

%macro Testloop;
  %do i = 1 %to %sysfunc(countw(&Sort.));
    %let SortID = %scan(&sort, &i);
    %let VariableA = %scan(&varlistA, &i);
    %let VariableE = %scan(&varlistE, &i);
    



/* Compute Variable Information for each Variable */
    data VariableInfo;
      set work.input_data;
      where Sort = &SortID.;
      drop Sort;
    run;
    
   %local Info1Items;
   %let Info1Items=;
   PROC SQL noprint;
      SELECT distinct Info1 INTO :Info1Items SEPARATED BY " "/*new*/
      FROM work.input_data
      WHERE Sort = &SortID.
      AND Info1 not in ('' 'Variable');
   QUIT;


   ods pdf startpage=now;
/*one row for variable report and one for the freq/mean reports as much columns as there are freq or means reports*/ ods layout gridded columns=%sysfunc(countw(&Info1Items.)) rows=2 ; ods region column=1 row=1; /* Report Variable Information for each Variable */ proc report data=VariableInfo noheader; column Info1 Info2; run; %MACRO PrinItemStats(Item=,colPosition=); ods region row=2; %if %sysfunc(exist(freq&Item.)) %then %do; /* Print Frequency Table */ proc report data=freq&Item.; column &Item. COUNT PERCENT; define &Item / display "&Item."; define COUNT / display "Absolut"; define PERCENT / display "Percent"; run; %end; %if %sysfunc(exist(mean&Item.)) %then %do; /* Print Mean Table */ proc report data=mean&Item.; column &Item.; run; %end; %MEND PrinItemStats; %local j currItem; %LET j=1; %LET currItem=%SCAN(&Info1Items.,&j.,%STR( )); %put ERROR:# &=Info1Items. %sysfunc(countw(&Info1Items.)); %DO %WHILE(%LENGTH(&currItem.)>0); %PrinItemStats(Item=&currItem., colPosition=&j.); %LET j=%EVAL(&j.+1); %LET currItem=%SCAN(&Info1Items.,&j.,%STR( )); %END; ods layout end; %end; %mend Testloop; ods _all_ close; options papersize=a4 orientation=landscape leftmargin=1.5cm rightmargin=1.5cm topmargin=1.5cm bottommargin=1.5cm nodate nonumber; ods pdf file="&yourpath.\LoopTest.pdf"; %Testloop; ods pdf close;
________________________

- Cheers -

View solution in original post

3 REPLIES 3
Ksharp
Super User

Maybe the following could you a help.

 

/*Here is an alternative way.*/
%let path= c:\temp ;
title;
options nodate nonumber papersize=(2in 2in) ;
ods printer printer=png file="&path.\a.png" style=journal;
proc report data=sashelp.class(obs=6 keep=name age) nowd;
run;

options nodate nonumber papersize=(2in 2in)  ;
ods printer printer=png file="&path.\b.png" style=journal ;
proc report data=sashelp.class(obs=6 keep=name sex) nowd;
run;

options nodate nonumber papersize=(2in 2in) ;
ods printer printer=png file="&path.\c.png" style=journal;
proc report data=sashelp.class(obs=6 keep=name weight) nowd;
run;


options papersize=A4  orientation=portrait;
ods pdf file="&path.\want.pdf" style=journal dpi=300 ;
data x;
x=' ';y=' ';z=' ';output;
x=' ';y=' ';z=' ';output;
run;
title;
proc report data=x nowd noheader style={outputwidth=100% rules=none frame=void};
column x y z;
define _all_/display style={cellwidth=30% bordercolor=white borderwidth=2};

compute z;
n+1;
if n=1 then do;
call define('y','style','style={ preimage="&path\a.png" bordertopcolor=white borderbottomcolor=white borderrightcolor=white borderleftcolor=white}');
end;
if n=2 then do;
call define('x','style','style={ preimage="&path\b.png" bordertopcolor=white borderbottomcolor=white borderrightcolor=white borderleftcolor=white}');
call define('z','style','style={ preimage="&path\c.png" bordertopcolor=white borderbottomcolor=white borderrightcolor=white borderleftcolor=white}');
end;
endcomp;
run;

ods pdf close;

Ksharp_0-1697885965073.png

 

Oligolas
Barite | Level 11

Hi,

 

I think you need to work with ods region.

There is a nice paper that may help you out here

 

As you see the alignment for the 'EZGH' Outputs is not left aligned since the 'second column' is here occupied by the 'Answer your item' column of the previous report.

You may want to merge all the means tables by hand prior to report them all at one on one table 


proc datasets lib=work nolist; delete sample_data input_data mean: VariableInfo freq: ;run ;quit;

%let yourpath=C:\;

ods listing;
/*** Example Data ***/
data work.sample_data;
  input EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;
  datalines;
  10 20 30 40 50  1 2 3 2 3
  15 25 35 45 55  1 3 4 3 2
  5  10 15 20 25  2 2 3 3 2
  20 30 40 50 60  4 3 2 1 1
  25 35 45 55 65  1 1 1 2 3
  ;
run;

data work.input_data;
   length Info1 $9 Info2 $16;
   input Info1 $ Info2 $ Sort;
   infile datalines delimiter=",";
   datalines;
   Variable,Answer your Item,1
   AGHK50A,Item A,1
   AGHK50B,Item B,1
   AGHK50C,Item C,1
   Variable,Answer your Item,2
   BGKO28A,Item A,2
   BGKO28B,Item B,2
   Variable,Answer your Item,3
   EZGH25A,Item A,3
   EZGH25B,Item B,3
   EZGH25C,Item C,3
   EZGH25D,Item D,3
   EZGH25E,Item E,3
;

/* Using proc sql for a Sorting Variable which is used to iterate trough the do-loop */
proc sql noprint;
   select distinct Sort into :Sort separated by " "
   from work.input_data;
quit;

%put &Sort; 

%let varlistE = EZGH25A EZGH25B EZGH25C EZGH25D EZGH25E;
%let varlistA = AGHK50A AGHK50B AGHK50C BGKO28A BGKO28B;

/* Mean Calculation */
proc means data=work.sample_data noprint;
  var &varlistE;
  output out=work.mean_ezgh;
run;

%macro meandata;
   %do i = 1 %to %sysfunc(countw(&varlistE));
   %let vmean = %scan(&varlistE, &i);
   
   data work.mean&vmean;
      set work.mean_ezgh (keep=&vmean);
   run;
   
   %end;
%mend meandata;
%meandata;

/* Freq calculation */
%macro freqdata;
   %do i = 1 %to %sysfunc(countw(&varlistA));
   %let vfreq = %scan(&varlista, &i);
   
   proc freq data=work.sample_data;
      tables &vfreq/ out=work.freq&vfreq;
   run;
   
   %end;
%mend freqdata;

%freqdata;


/*** Macro Loop ***/

%macro Testloop;
  %do i = 1 %to %sysfunc(countw(&Sort.));
    %let SortID = %scan(&sort, &i);
    %let VariableA = %scan(&varlistA, &i);
    %let VariableE = %scan(&varlistE, &i);
    



/* Compute Variable Information for each Variable */
    data VariableInfo;
      set work.input_data;
      where Sort = &SortID.;
      drop Sort;
    run;
    
   %local Info1Items;
   %let Info1Items=;
   PROC SQL noprint;
      SELECT distinct Info1 INTO :Info1Items SEPARATED BY " "/*new*/
      FROM work.input_data
      WHERE Sort = &SortID.
      AND Info1 not in ('' 'Variable');
   QUIT;


   ods pdf startpage=now;
/*one row for variable report and one for the freq/mean reports as much columns as there are freq or means reports*/ ods layout gridded columns=%sysfunc(countw(&Info1Items.)) rows=2 ; ods region column=1 row=1; /* Report Variable Information for each Variable */ proc report data=VariableInfo noheader; column Info1 Info2; run; %MACRO PrinItemStats(Item=,colPosition=); ods region row=2; %if %sysfunc(exist(freq&Item.)) %then %do; /* Print Frequency Table */ proc report data=freq&Item.; column &Item. COUNT PERCENT; define &Item / display "&Item."; define COUNT / display "Absolut"; define PERCENT / display "Percent"; run; %end; %if %sysfunc(exist(mean&Item.)) %then %do; /* Print Mean Table */ proc report data=mean&Item.; column &Item.; run; %end; %MEND PrinItemStats; %local j currItem; %LET j=1; %LET currItem=%SCAN(&Info1Items.,&j.,%STR( )); %put ERROR:# &=Info1Items. %sysfunc(countw(&Info1Items.)); %DO %WHILE(%LENGTH(&currItem.)>0); %PrinItemStats(Item=&currItem., colPosition=&j.); %LET j=%EVAL(&j.+1); %LET currItem=%SCAN(&Info1Items.,&j.,%STR( )); %END; ods layout end; %end; %mend Testloop; ods _all_ close; options papersize=a4 orientation=landscape leftmargin=1.5cm rightmargin=1.5cm topmargin=1.5cm bottommargin=1.5cm nodate nonumber; ods pdf file="&yourpath.\LoopTest.pdf"; %Testloop; ods pdf close;
________________________

- Cheers -

_Manhattan
Obsidian | Level 7

Cheers Oligolas,

 

again very helpful input from you, I appreciate it!

 

Best regards

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 647 views
  • 1 like
  • 3 in conversation