09-21-2017 09:51 PM
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:
09-22-2017 04:18 AM
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
09-22-2017 11:42 AM
- 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.
If x=3 then newvar=5;
will add newvar to all records but the only values would be when x=3 and have 5.
12-22-2017 05:44 AM - edited 12-22-2017 04:00 PM
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