topic Re: SAS 9.4: Insert a blank row based on condition in SAS Procedures

SAS 9.4: Insert a blank row based on condition

1239 — Mon, 21 Jul 2014 10:54:58 GMT

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;

Re: SAS 9.4: Insert a blank row based on condition

data_null__ — Mon, 21 Jul 2014 11:08:17 GMT

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;

Re: SAS 9.4: Insert a blank row based on condition

Ksharp — Mon, 21 Jul 2014 13:25:18 GMT

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

Re: SAS 9.4: Insert a blank row based on condition

1239 — Tue, 22 Jul 2014 04:45:34 GMT

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;

Re: SAS 9.4: Insert a blank row based on condition

Patrick — Tue, 22 Jul 2014 05:57:07 GMT

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;

Re: SAS 9.4: Insert a blank row based on condition

Ksharp — Tue, 22 Jul 2014 12:04:21 GMT

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

Re: SAS 9.4: Insert a blank row based on condition

Ksharp — Tue, 22 Jul 2014 12:18:31 GMT

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