BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
D-e-e
Calcite | Level 5
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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Astounding
PROC Star

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.

D-e-e
Calcite | Level 5
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;'?
ballardw
Super User

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.

Astounding
PROC Star

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.

D-e-e
Calcite | Level 5

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
Astounding
PROC Star

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

D-e-e
Calcite | Level 5
This is it! Thanks a lot!
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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