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!
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.
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.
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.
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.
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 |
I made one change in the original program ... see if that does the trick.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.