topic Re: SAS: Insert multiple columns or rows in a specific position of an existing dataset in SAS Programming

SAS: Insert multiple columns or rows in a specific position of an existing dataset

yescobar — Fri, 22 Sep 2017 01:51:22 GMT

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:

Is there a better way to do this for rows?
Is there an equivalent for columns?
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?

Re: SAS: Insert multiple columns or rows in a specific position of an existing dataset

ChrisBrooks — Fri, 22 Sep 2017 08:18:33 GMT

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

Re: SAS: Insert multiple columns or rows in a specific position of an existing dataset

ballardw — Fri, 22 Sep 2017 15:42:30 GMT

@yescobar wrote:

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

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.

Re: SAS: Insert multiple columns or rows in a specific position of an existing dataset

Hsej — Fri, 22 Dec 2017 21:00:53 GMT

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 🙂