Hi,
This is the 3rd question that I am posting from the same series - its too much to put into one question so I decided to break into several questions.
Suppose I have the following table:
company | year | cond1 | cond2 |
---|---|---|---|
A | 2006 | 1 | 2 |
A | 2009 | 1 | 2 |
B | 2005 | 3 | 4 |
B | 2010 | 3 | 6 |
B | 2012 | 3 | 6 |
In this case if two adjacent years for the same company have the same conditions then leave as is.
If two adjacent years have different conditions, then the year after the first year will have the same conditions as the first year. In this example, for Bditional year 2006 should be created with conditions (3,4)
So the new table should look like:
company | year | cond1 | cond2 |
---|---|---|---|
A | 2006 | 1 | 2 |
A | 2009 | 1 | 2 |
B | 2005 | 3 | 4 |
B | 2006 | 3 | 4 |
B | 2010 | 3 | 6 |
B | 2012 | 3 | 6 |
Thank you!
Next time attempt to solve the problem BEFORE asking for advice. You should have been able to figure this out by now:
data have;
infile cards dlm='09'x;
input company $ year cond1 cond2;
cards;
A 2006 1 2
A 2009 1 2
B 2005 3 4
B 2010 3 6
B 2012 3 6
;
data want;
set have end=eof;
by company cond1 cond2 notsorted;
if last.cond2 and not last.company then do;
set have(firstobs=2 keep=year rename=year=nextyear);
output;
if year+1 ne nextyear then do;
year=year+1;
output;
end;
end;
else output;
drop nextyear;
run;
Next time attempt to solve the problem BEFORE asking for advice. You should have been able to figure this out by now:
data have;
infile cards dlm='09'x;
input company $ year cond1 cond2;
cards;
A 2006 1 2
A 2009 1 2
B 2005 3 4
B 2010 3 6
B 2012 3 6
;
data want;
set have end=eof;
by company cond1 cond2 notsorted;
if last.cond2 and not last.company then do;
set have(firstobs=2 keep=year rename=year=nextyear);
output;
if year+1 ne nextyear then do;
year=year+1;
output;
end;
end;
else output;
drop nextyear;
run;
Thanks Arthur for your answer!
I know that it is very similar to my previous questions, its just that I have some trouble intuiting the code, but I am trying to figure that out
Thanks again!
Just think through the process. In the present case if the data are already sorted by company and year, and then set (in the data step) with a by statement (namely by company cond1 cond2 notsorted), then:
last.company will only be equal to 1 when the data step gets to the last record for each company.
last.cond2 will only be equal to 1 when the data step get to a record where it is either the last record for a company or where the next record for a company has a different cond1 or cond2
set have(firstobs=2 keep=year rename=year=nextyear); creates a variable called nextyear that shows the year shown on the next record.
Thus, the code outputs all records and, if the current record isn't the last record for a company AND the next record has a different cond1 or cond2 AND if the next record's year is greater than year+1, then output a new record with year=year+1.
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 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.