BookmarkSubscribeRSS Feed
1239
Calcite | Level 5

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;

6 REPLIES 6
data_null__
Jade | Level 19

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;

Ksharp
Super User
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

1239
Calcite | Level 5

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;

Ksharp
Super User

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

Patrick
Opal | Level 21

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;

Ksharp
Super User

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

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!

What is Bayesian Analysis?

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.

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
  • 6 replies
  • 16991 views
  • 2 likes
  • 4 in conversation