Help using Base SAS procedures

SAS 9.4: Insert a blank row based on condition

Reply
Contributor
Posts: 55

SAS 9.4: Insert a blank row based on condition

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

CountryProductYear
USAMobile2000
USALaptop2012
USATablet2013
GermanyPC2010
GermanyLaptop2009
IndiaHard Disk2011
IndiaSmart Phone2008
IndiaLaptop2007
IndiaPC2006
IndiaTablet2005

--Desired Output

CountryProductYear
USAMobile2000
USALaptop2012
USATablet2013
GermanyPC2010
GermanyLaptop2009
IndiaHard Disk2011
IndiaSmart Phone2008
IndiaLaptop2007
IndiaPC2006
IndiaTablet2005


--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;

Respected Advisor
Posts: 3,799

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

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;

Super User
Posts: 10,035

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

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

Contributor
Posts: 55

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

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;

Super User
Posts: 10,035

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

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

Respected Advisor
Posts: 4,173

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

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;

Super User
Posts: 10,035

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

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

Ask a Question
Discussion stats
  • 6 replies
  • 9825 views
  • 1 like
  • 4 in conversation