Hi,
I want to insert blank row after every Country in my report. I referred the below sample code wherein it is inserting every 5th row, however, my requirement is to insert rows whenever country changes. Please help me.
-- Input Data
Country | Product | Year |
USA | Mobile | 2000 |
USA | Laptop | 2012 |
USA | Tablet | 2013 |
Germany | PC | 2010 |
Germany | Laptop | 2009 |
India | Hard Disk | 2011 |
India | Smart Phone | 2008 |
India | Laptop | 2007 |
India | PC | 2006 |
India | Tablet | 2005 |
--Desired Output
Country | Product | Year |
USA | Mobile | 2000 |
USA | Laptop | 2012 |
USA | Tablet | 2013 |
Germany | PC | 2010 |
Germany | Laptop | 2009 |
India | Hard Disk | 2011 |
India | Smart Phone | 2008 |
India | Laptop | 2007 |
India | PC | 2006 |
India | Tablet | 2005 |
--Sample Code
/* Insert blank observation after every 5 observations */
data class_blanks;
set sashelp.class;
output; /* Output real observation */
if mod(_n_,5)=0;
array allnums {*} _numeric_ ;
array allchar {*} _character_ ;
drop i;
do i=1 to dim(allnums); allnums{i}=.; end;
do i=1 to dim(allchar); allchar{i}=' '; end;
output; /* Output blank observation */
run;
options missing=' '; /* Display numeric missing as blank */
proc print data=class_blanks noobs;
title 'SASHELP.CLASS with Blank Line After Every 5 Obs';
run;
This seems more like something you would do in a report than to add to your data set.
I created a new variable to order the obs within each country that may be helpful or you can omit.
data country;
infile cards expandtabs;
input Country $ Product &$16. Year;
cards;
USA Mobile 2000
USA Laptop 2012
USA Tablet 2013
Germany PC 2010
Germany Laptop 2009
India Hard Disk 2011
India Smart Phone 2008
India Laptop 2007
India PC 2006
India Tablet 2005
;;;;
run;
data want;
set country;
by country notsorted;
if first.country then row=0;
row+1;
output;
if last.country then do;
_n_=row;
call missing(of _all_);
row = _n_+1;
output;
end;
run;
data country; infile cards expandtabs; input Country $ Product &$16. Year; cards; USA Mobile 2000 USA Laptop 2012 USA Tablet 2013 Germany PC 2010 Germany Laptop 2009 India Hard Disk 2011 India Smart Phone 2008 India Laptop 2007 India PC 2006 India Tablet 2005 ;;;; run; data want(drop=_:); merge country country(firstobs=2 keep=Country rename=(Country=_country)); output; if Country ne _country then do; call missing(of _all_);output; end; run;
Xia Keshan
Thanks for the code. It's working fine however, I don't want to insert a blank row after last Country(India) in my report. From the above code, blank row is inserting after Country (India) wherein I don't want to insert blank row after India so I have tried the below code to delete last row from the dataset but one additional dataset will be created and if possible I need to avoid this additional step. Please let me know is there any alternative code can be written in efficient manner or from your code itself this can be excluded?
data want_new;
set want end=last;
if last then delete;
run;
OK. No problem.
data country; infile cards expandtabs; input Country $ Product &$16. Year; cards; USA Mobile 2000 USA Laptop 2012 USA Tablet 2013 Germany PC 2010 Germany Laptop 2009 India Hard Disk 2011 India Smart Phone 2008 India Laptop 2007 India PC 2006 India Tablet 2005 ;;;; run; data want(drop=_:); merge country country(firstobs=2 keep=Country rename=(Country=_country)); output; if Country ne _country and not missing(_country) then do; call missing(of _all_);output; end; run;
Xia Keshan
Message was edited by: xia keshan
I don't think it's a good practice to add empty rows to your data only to meet reporting requirements. Treat data organisation and report layout requirements as something separate.
Below code gives you something close to what you've asked for. I'm not a very experienced Proc Report user so I'm sure there are people out there who can support you to tweak the layout further if required.
data country;
infile datalines dsd dlm=',';
input Country :$16. Product :$16. Year;
datalines;
USA,Mobile,2000
USA,Laptop,2012
USA,Tablet,2013
Germany,PC,2010
Germany,Laptop,2009
India,Hard Disk,2011
India,Smart Phone,2008
India,Laptop,2007
India,PC,2006
India,Tablet,2005
;;;;
run;
proc report data=WORK.country nowd;
column Country Product Year cv1;
define Country / order 'Country' missing;
compute Country;
if Country ne ' ' then hold1=Country;
if Country eq ' ' then Country=hold1;
endcomp;
define Product / order 'Product' missing;
compute Product;
if Product ne ' ' then hold2=Product;
if Product eq ' ' then Product=hold2;
endcomp;
define Year / order missing noprint;
define cv1 / computed 'Year' missing;
compute cv1;
if Year ne . then hold3=Year;
cv1=hold3;
endcomp;
compute before country;
line ' ';
endcomp;
run;
quit;
Patrick ,
If it is for proc Report , you can do it as simple as :
data country; infile cards expandtabs; input Country $ Product &$16. Year; cards; USA Mobile 2000 USA Laptop 2012 USA Tablet 2013 Germany PC 2010 Germany Laptop 2009 India Hard Disk 2011 India Smart Phone 2008 India Laptop 2007 India PC 2006 India Tablet 2005 ;;;; run; ods html file='x.html' style=sasweb; proc report data=WORK.country nowd out=x; column Country Product Year ; define Country / group 'Country' missing; define Product / display 'Product' missing; define Year / order missing ; compute year; if _break_ eq 'Country' and country='USA' then len=0; else len=20; endcomp; compute after country; a=' '; line a $varying200. len; endcomp; run;
Regards
Xia Keshan
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.