Desktop productivity for business analysts and programmers

Insert rows into table with sequences

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Insert rows into table with sequences

monthIDCODEAMOUNT
110510000
21059500
51058000
502024000
522023000
572021500
4220320000
4320319000

 

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
3 weeks ago
Respected Advisor
Posts: 4,955

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.

View solution in original post


All Replies
Solution
3 weeks ago
Respected Advisor
Posts: 4,955

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.

New Contributor
Posts: 4

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;'?
Grand Advisor
Posts: 10,196

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.

Respected Advisor
Posts: 4,955

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.

New Contributor
Posts: 4

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):

monthIDCODEAMOUNT
110510000
21059500
31050
41050
51058000
502024000
512020
522023000
532020
542020
552020
562020
572021500
4220320000
4320319000
Respected Advisor
Posts: 4,955

Re: Insert rows into table with sequences

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

New Contributor
Posts: 4

Re: Insert rows into table with sequences

This is it! Thanks a lot!
Grand Advisor
Posts: 9,567

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 245 views
  • 1 like
  • 4 in conversation