topic Re: Insert rows into table with sequences in SAS Enterprise Guide

Insert rows into table with sequences

D-e-e — Wed, 28 Jun 2017 21:10:09 GMT

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!

Re: Insert rows into table with sequences

Astounding — Thu, 29 Jun 2017 11:08:12 GMT

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.

Re: Insert rows into table with sequences

D-e-e — Wed, 28 Jun 2017 21:45:08 GMT

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

Re: Insert rows into table with sequences

ballardw — Wed, 28 Jun 2017 22:25:45 GMT

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.

Re: Insert rows into table with sequences

Astounding — Wed, 28 Jun 2017 23:06:16 GMT

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.

Re: Insert rows into table with sequences

D-e-e — Thu, 29 Jun 2017 09:09:27 GMT

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

Re: Insert rows into table with sequences

Astounding — Thu, 29 Jun 2017 11:09:27 GMT

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

Re: Insert rows into table with sequences

D-e-e — Thu, 29 Jun 2017 12:31:05 GMT

This is it! Thanks a lot!

Re: Insert rows into table with sequences

Ksharp — Thu, 29 Jun 2017 13:22:53 GMT

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;