Add a new row before last row for each group in SAS data step

[Moksha]

Hi All,

       I have the following data set:

data have;
	input subjid visit$ aval;
cards;
101 screen 20
101 base   25
101 v1     25
101 v2     26
102 screen 25
102 base   30
102 v1     25
102 v2     30
103 screen 23
103 base   33
;
run;

I want to have the above highlighted rows in blue color inserted as below:

data want;
	input subjid visit$ aval;
cards;
101 screen 20
101 base   25
101 v1     25
101 base   25
101 v2     26
102 screen 25
102 base   30
102 v1     25
102 base   30
102 v2     30
103 screen 23
103 base   33
103 v1     20
103 base   33
103 v2     25
;
run;	

Can anyone help?

Thanks

[PaigeMiller]

No blue rows appear.

Could you explain the logic used in determining where the rows should go? The very brief description in the title doesn't seem to match what I see in your desired output. Also, you use the word "group" but don't define it.

[Moksha]

Here, is the data set that I have:

data have;
input subjid visit$ aval;
cards;
101 screen 20
101 base 25
101 v1 25
101 v2 26
102 screen 25
102 base 30
102 v1 25
102 v2 30
103 screen 23
103 base 33
;
run;

There are 3 subjid's 101, 102 and 103. For each of these subjid group values, the row with visit as base is highlighted in blue color.

I want the above blue color highlighted rows inserted in between within each subjid group as follows and for subjid 103 since there are no rows for visits v1 and v2 and hence need to add these rows also:

data want;

input subjid visit$ aval;

cards;

101 screen 20

101 base   25

101 v1     25

101 base   25

101 v2     26

102 screen 25

102 base   30

102 v1     25

102 base   30

102 v2     30

103 screen 23

103 base   33

103 v1     20

103 base   33

103 v2     25

;

run;

Then newly inserted rows are highlighted in Amber color.

Thanks

[PaigeMiller]

Okay, I think I see a pattern in the output; but I really really really want you to tell me what the pattern is and what the logic is; instead of me trying to figure it out (and possibly getting it wrong). And you have not said what determines where these extra rows will be added.

Please tell us what you know, that is the best way to get the help you need. Please don't make us guess, please don't make us try to figure it out, we could possibly get it wrong and that is not a good way to get the help you need. (And this applies not only to this question, but to all questions you ask in the future, please tell us what you know, don't make us guess, and don't make us ask for the logic or details to be explained).

[Moksha]

It's that I want to insert a row containing the base value for subjid before their visit 2 i.e., v2. I am just checking how to carry forward this row and insert it. 

If visit 1 and visit 2 are missing for any subjid (in this example, they are missing for subjid 103), then create these two rows for that subjid (here aval can have any value as it is just for test purpose) and insert the row with base visit before the row of visit 2.

[mkeintz]

In dataset WANT, for subjid 103, you put a V! record with aval=20 and a V2 record with aval=25, but where did these aval values come from in your dataset HAVE?

The process for subjid 101 and 102 is clear, but not for 103.

[Moksha]

For 103 v1 and v2, since it's just for testing purpose, I have used some example values for aval.

[Moksha]

If anyone can help me to achieve the following, would be of great help. In this, I have excluded the requirement for subjid 103 v1 and v2.

data have;
input subjid visit$ aval;
cards;
101 screen 20
101 base 25
101 v1 25
101 v2 26
102 screen 25
102 base 30
102 v1 25
102 v2 30
103 screen 23
103 base 33
;
run;

There are 3 subjid's 101, 102 and 103. For subjid 101 and 102, the row with visit as base is highlighted in blue color. Since, subjid 103 does not have v1 and v2, I have not highlighted the base row is not highlighted.

I want the above blue color highlighted rows inserted in between within each subjid group as follows:

data want;

input subjid visit$ aval;

cards;

101 screen 20

101 base   25

101 v1     25

101 base   25

101 v2     26

102 screen 25

102 base   30

102 v1     25

102 base   30

102 v2     30

103 screen 23

103 base   33

;

run;

Then newly inserted rows are highlighted in Amber color.

[Moksha]

I have tried the following but, it is inserting a new row for subjid 101 only and not for subjid 102. But, I want to insert a new record for subjid 102 also.

Here is the code that I have tried:

data have1;
	set visit_sorted;
	by subjid visitn;
	output;

	retain _basesubjid _basevisit _baseaval _basevisitn;

	if visit = 'base' and aval ne . then do;
		_basesubjid = subjid;
		_basevisit  = visit;
		_baseaval   = aval;
		_basevisitn = visitn;
	end;

	if _n_= 3 then do;
		subjid = _basesubjid;
		visit = _basevisit;
		aval  = _baseaval;
		visitn = _basevisitn;
		dtype = 'BOCF';
		output;
	end;
drop _basesubjid _basevisit _baseaval _basevisitn;
run;

proc print;run;

If anyone can help to insert the row for subjid 102 record also, it would be of great help. For subjid 102, following record highlighted in 'Red' color to be inserted between v1 and v2 records.

data have;
input subjid visit$ aval;
cards;
101 screen 20
101 base 25
101 v1 25
101 v2 26
102 screen 25
102 base 30
102 v1 25
102 v2 30
103 screen 23
103 base 33
;
run;

[Moksha · Accepted solution]

By hardcoding for v1, I could get the desire result and here is the code:

data have1;
	set visit_sorted;
	by subjid visitn;
	output;

	retain _basesubjid _basevisit _baseaval _basevisitn;

	if visit = 'base' and aval ne . then do;
		_basesubjid = subjid;
		_basevisit  = visit;
		_baseaval   = aval;
		_basevisitn = visitn;
	end;

/*	if _n_= 3 then do;*/
	if visit = 'v1' then do;
		subjid = _basesubjid;
		visit = _basevisit;
		aval  = _baseaval;
		visitn = _basevisitn;
		dtype = 'BOCF';
		output;
	end;
drop _basesubjid _basevisit _baseaval _basevisitn;
run;

proc print;run;

If there is any other better way to achieve this, please let me know.  Actually, I want know if there is any other way to achieve without hardcoding as above.

Result of the above code:

Obs subjid visit aval visitn dtype 
1 101 screen 20 1   
2 101 base 25 2   
3 101 v1 25 3   
4 101 base 25 2 BOCF 
5 101 v2 26 4   
6 102 screen 25 1   
7 102 base 30 2   
8 102 v1 25 3   
9 102 base 30 2 BOCF 
10 102 v2 30 4   
11 103 screen 23 1   
12 103 base 33 2