BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26
proc sort data=sashelp.class out=class;
by sex;
run;
ods excel file="text.xlsx";
proc report data=class;
columns sex age height weight;
run;
ods excel close;

How can I instruct PROC REPORT to produce a thick line between the last F and first M record? How can I instruct PROC REPORT to produce a thick line above the first F record? How can I instruct PROC REPORT to produce a thick line below the last M record? It should look like this, I made the lines thick using Excel, but I want to do in SAS:

 

PaigeMiller_1-1666705644242.png

 

--
Paige Miller
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
proc sort data=sashelp.class out=class;
by sex;
run;

%let dsid=%sysfunc(open(class));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
 
ods excel file="c:\temp\text.xlsx" ;
proc report data=class nowd   ;
columns sex age height weight;
compute weight;
n+1;
if sex ne lag(sex) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if  n=&nobs. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

Ksharp_0-1666783923504.png

 

View solution in original post

7 REPLIES 7
Ksharp
Super User
proc sort data=sashelp.class out=class;
by sex;
run;

%let dsid=%sysfunc(open(class));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
 
ods excel file="c:\temp\text.xlsx" ;
proc report data=class nowd   ;
columns sex age height weight;
compute weight;
n+1;
if sex ne lag(sex) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if  n=&nobs. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

Ksharp_0-1666783923504.png

 

PaigeMiller
Diamond | Level 26

Great job, @Ksharp . Thanks! I was not aware that LAG would work in a COMPUTE block in PROC REPORT.

--
Paige Miller
PaigeMiller
Diamond | Level 26

Ok, again thank you @Ksharp but I seem to have fallen into a trap that others fall into. I gave a data set that didn't illustrate the real problem. Sorry about that.

 

So how do I get this to work with two group variables and an across variable, with thick lines between the different values of TYPE? The code below doesn't work, the thick lines are not in the right spots.

 

 

%let dsid=%sysfunc(open(sashelp.cars));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
%put &=nobs;

ods excel file="c:\temp\text.xlsx" ;
proc report data=sashelp.cars nowd   ;
columns type drivetrain origin,(enginesize horsepower) _dummy;
define type/group;
define drivetrain/group;
define origin/across;
define enginesize/mean;
define horsepower/mean;
define _dummy/noprint;
compute _dummy;
n+1;
if type ne lag(type) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if n=&nobs. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

 

 

Here is what I want the report to look like, but I did this in Excel, I want to know how to do this in SAS.

PaigeMiller_0-1666787926664.png

 

--
Paige Miller
Ksharp
Super User

OK Paige,

How about this one ?

 

Opps, I missed one thing the black line under header.

@PaigeMiller  code has been updated.

 

proc sort data=sashelp.cars out=have;
by type drivetrain origin;
run;


proc freq data=have noprint;
table type*drivetrain /out=temp;
run;
data _null_;
 set temp end=last;
 by type;
 length nobs $ 100;
 retain nobs;
 if first.type then nobs=catx(' ',nobs,_n_);
 if last then do;call symputx('nobs',nobs);call symputx('n',_n_);end;
run;
%put &=nobs.;


ods excel file="c:\temp\text.xlsx" ;
proc report data=have nowd ;
columns type drivetrain origin,(enginesize horsepower) ;
define type/group;
define drivetrain/group;
define origin/across;
define enginesize/mean;
define horsepower/mean;
compute type;
n+1;
if n in ( &nobs.) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if n = &n. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;

Ksharp_0-1666789940278.png

 

 

PaigeMiller
Diamond | Level 26

Excellent! That works, and is perfectly general so I can apply it to similar problems.

 

As a side comment (I'm not asking for additional code since this works) I am surprised the solution is not done entirely in PROC REPORT, which is where I got stuck, I thought it would be possible, but I never got close.

--
Paige Miller
Ksharp
Super User

@PaigeMiller 

Sorry . Actually PROC REPORT could get job done.

Here code is simpler .

 


proc sql noprint;
select count(*) into :nobs
 from (select distinct  type,drivetrain from sashelp.cars) ;
quit;

ods excel file="c:\temp\text.xlsx" ;
proc report data=sashelp.cars nowd   ;
columns type drivetrain origin,(enginesize horsepower) ;
define type/group;
define drivetrain/group;
define origin/across;
define enginesize/mean;
define horsepower/mean;
compute type;
n+1;
if not missing(type) then call define(_row_,'style','style={bordertopcolor=black bordertopwidth=8}');
if n=&nobs. then call define(_row_,'style','style={borderbottomcolor=black borderbottomwidth=8}');
endcomp;
run;
ods excel close;
PaigeMiller
Diamond | Level 26

Thank you again!

 

One thing you did in this code, which I didn't think of when I was trying to get this to work by myself, is that you have realized (which I didn't realize) that making a thick line above a certain cell is the same as going to the cell above it and making a thick line below that cell. Once I have that idea, yes, everything you did makes perfect sense and seems quite easy now.

--
Paige Miller

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!

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
  • 7 replies
  • 776 views
  • 5 likes
  • 2 in conversation