BookmarkSubscribeRSS Feed
yescobar
Calcite | Level 5

Goal: Add one or more empty rows after a specific position in the dataset

Here's the work I've done so far:

 

data test_data;
    set sashelp.class;
    output;

    /* Add a blank line after row 5*/
    if _n_ = 5 then do;
        call missing(of _all_);
        output;
    end;

    /* Add 4 blank rows after row 7*/

    if _n_ = 7 then do;
        /* inserts a blank row after row 8 (7 original rows + 1 blank row) */
        call missing(of _all_);

        /*repeats the newly created blank rows: inserts 3 blank rows*/
        do i = 1 to 3;
            output;
        end;
    end;
run;

 

I'm still learning how to use SAS, but I "feel" like there's a better way to get to the same result, chiefly not having to use a for loop to insert multiple empty rows. I was wondering:

 

  1. Is there a better way to do this for rows?
  2. Is there an equivalent for columns?
  3. These rows/columns are being added more to fit a report format. The dataset doesn't need these blank rows/columns for its own sake. Is some PROC or REPORT function that achieves the same thing?

 

3 REPLIES 3
ChrisBrooks
Ammonite | Level 13

Firstly I'd NEVER insert empty rows or columns in a data set just to make a report "fit". It makes any further processing of the data much more difficult and is simply unnecessary. Proc Report is incredibly versatile and give you just about any layout you want - for example this paper shows how you can simulate empty columns in your report http://www2.sas.com/proceedings/sugi31/092-31.pdf

 

You can also simulate blank lines based on a condition using similar techniques

ballardw
Super User

@yescobar wrote:

 

  1. Is there an equivalent for columns?

 

SAS datasets every record will have the same number of variables in the output. So if you are thinking of "conditional" variables then no.

If you add any statemen using variable then that varaible will be created if it does not alread and have a missing value unless assigned one.

Label BlankVar='this is a blank';

or

Format BlankVar best5.;

or an attribute statement, using the variable name on an array reference, retain or any number of other statements

will add a variable and get a message in the log about variable blankvar has never been referenced if you do not assign a value.

A conditional:

If x=3 then newvar=5;

will add newvar to all records but the only values would be when x=3 and have 5.

Hsej
Fluorite | Level 6

Regarding your first question: Is there a better way to do this for rows? I have not found a better solution on the web. However, I have used your solution to insert multiple rows between two existing rows (current and previous row).

 

Here is my code:

 

/*
Convert year/week variable to a date (Monday in week)
*/
%macro year_week_to_date(year_week, active_libname, table_input, table_output);
	proc sql;
		create table &active_libname..&table_output as
		select
		*,
		intnx(
			'week.2',
			(
				case when week(mdy(1, 1, input(substr(&year_week, 1, 4), 4.)), 'v') eq 1
				then intnx('week.1', mdy(1, 1, input(substr(&year_week, 1, 4), 4.)), 0)
				else
				mdy(1, 1, input(substr(&year_week, 1, 4), 4.))
				end
			),
			input(substr(&year_week, 5, 2), 2.)
		) as year_week_date
		format ddmmyyd10.
		from &active_libname..&table_input;
	quit;
%mend;

options mprint;
%year_week_to_date(year_week, work, any_table, temp0);

/*
Sort data
*/
proc sort data=work.temp0 out=work.temp1;
	by a b c d e year_week_date;
run;

/*
Flag the current row if the difference between the current and previous row (within group) is greater or equal to one week
*/
data work.temp2;
	set work.temp1;
	by a b c d e year_week_date;

	group = 0;
	delta = intck('week', lag(year_week_date), year_week_date) - 1;

	if lag(a) eq a
	and lag(b) eq b
	and lag(c) eq c
	and lag(d) eq d
	and lag(e) eq e then group = 1;

	if group eq 1 and delta eq 0 then break = 0;
	else if group eq 1 and delta ge 1 then break = 1;
	else break = 0;
run;

/*
Sort data
*/
proc sort data=work.temp2 out=work.temp3;
	by a b c d e descending year_week_date;
run;

/*
Insert new row(s) and update column values
Insert a maximum of 4 rows (business rule)
*/
data work.temp4;
	set work.temp3;
	by a b c d e descending year_week_date;
	output;
	if break eq 1 then
		do;
			do i = 1 to delta until(i = 4);
				* Flag a new row;
				break = 2;
				year_week_date = intnx('week.2', year_week_date, - 1);
				f = 'No delivery';
				g = 0;
				output;
			end;
		end;
run;

/*
Convert year_week_date to year/week and replace the old year/week variable
*/
data work.temp5;
	set work.temp4;
	year_week =
		put(
			year(year_week_date) - (month(year_week_date) = 1 and week(year_week_date, 'v') in(52, 53)) + (month(year_week_date) = 12 and week(year_week_date, 'v') = 1),
			4.
		)
		||
		put(
			week(year_week_date, 'v'),
			z2.
		);
run;

/*
Sort data
*/
proc sort data=work.temp5 out=work.output;
	by a b c d e year_week_date;
run;

 

Thank you very much for your help 🙂

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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