Insert rows into table with sequences

Solved
Occasional Contributor
Posts: 8

Insert rows into table with sequences

 monthID CODE AMOUNT 1 105 10000 2 105 9500 5 105 8000 50 202 4000 52 202 3000 57 202 1500 42 203 20000 43 203 19000

I have to insert rows between monthIDs to have, in example, also monthID 3 and 4 for CODE 105. When inserting monthIDs in order, the CODE must be the same as previous (105 for this example) and the AMONUT must be 0. There is a lot of data so I cannot write which data to insert, it must be somehow automatically managed.

Thx!

Accepted Solutions
Solution
‎06-29-2017 08:20 AM
Super User
Posts: 6,935

Re: Insert rows into table with sequences

[ Edited ]

Here are a couple of assumptions.  Your data should be sorted by CODE.  And MONTHID should be a numeric variable.  In that case:

data want;

set have;

by CODE;

output;

if last.CODE=0;

_n_ = _n_ + 1;

set have (keep=monthID rename=(monthID = next_month)) point=_n_;

if next_month - monthID > 1;

amount=0;

do monthID = monthID + 1 to next_month - 1;

output;

end;

drop next_month;

run;

The code is untested, but we can clean it up pretty easily if you run into any problems with it.

All Replies
Solution
‎06-29-2017 08:20 AM
Super User
Posts: 6,935

Re: Insert rows into table with sequences

[ Edited ]

Here are a couple of assumptions.  Your data should be sorted by CODE.  And MONTHID should be a numeric variable.  In that case:

data want;

set have;

by CODE;

output;

if last.CODE=0;

_n_ = _n_ + 1;

set have (keep=monthID rename=(monthID = next_month)) point=_n_;

if next_month - monthID > 1;

amount=0;

do monthID = monthID + 1 to next_month - 1;

output;

end;

drop next_month;

run;

The code is untested, but we can clean it up pretty easily if you run into any problems with it.

Occasional Contributor
Posts: 8

Re: Insert rows into table with sequences

Thank you very much for the answer. Your assumptions are correct. I will be able to test the code tomorrow, but since I am new in this, could you please explain the line
'if last.CODE=0;'?
Super User
Posts: 13,942

Re: Insert rows into table with sequences

When you use a BY statement in a data step SAS creates automatic variables that indicate whether the current value of a variable is associated with the first or last of that value. The value is 1 if it is true (either the first or the last) and 0 otherwise. You access that value with the First. and Last.  (the dot is important!) notation.

So 'if last.CODE=0;' is equivalent to "if not the last of the current Code value group";

A simple IF with no 'then' after it is a subsetting if. So only records where that statement is true are written to the output data set.

Super User
Posts: 6,935

Re: Insert rows into table with sequences

What he said, except for one key feature.

Note that there is an OUTPUT statement (two in fact) before the subsetting IF.  So every observation will be part of your output ... nothing gets deleted.  Try it and see whether it does what you want.

Occasional Contributor
Posts: 8

Re: Insert rows into table with sequences

[ Edited ]

Hi, the code doesn't work fine. Lines are duplicated and there is no added monthID. I tried to transform CODE to numeric variable but the problem persists. Should we define somewhere that the new added line should have the same CODE as previous?

So, my data shoudl be like (bolded lines must be added to the original dataset):

 monthID CODE AMOUNT 1 105 10000 2 105 9500 3 105 0 4 105 0 5 105 8000 50 202 4000 51 202 0 52 202 3000 53 202 0 54 202 0 55 202 0 56 202 0 57 202 1500 42 203 20000 43 203 19000
Super User
Posts: 6,935

Re: Insert rows into table with sequences

I made one change in the original program ... see if that does the trick.

Occasional Contributor
Posts: 8

Re: Insert rows into table with sequences

This is it! Thanks a lot!
Super User
Posts: 10,852

Re: Insert rows into table with sequences

``````data have;
infile cards expandtabs truncover;
input monthID	CODE	AMOUNT;
cards;
1	105	10000
2	105	9500
5	105	8000
50	202	4000
52	202	3000
57	202	1500
42	203	20000
43	203	19000
;
run;

data want;
merge have have(keep=monthid code rename=(monthid=month code=_code) firstobs=2);
output;
if code=_code then do;
do i=monthid+1 to month-1;
monthid=i; amount=0;output;
end;
end;
drop month i _code;
run;``````
☑ This topic is solved.