Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
If so can I ask for a little help please. I have bitten off more than I can chew...SAS programming (well all programming) is a little alien to me, I have got my head round the SQL part of it but I don't think SQL is appropriate for the next bit. What I want to do isn't difficult, I have managed it in VBA, unfortunately I have no idea where to start in SAS. Any tips or pointers greatly appreciated.
I have a list of dates for a particular groups, so A could have 4 dates, B 10, C 1 etc. I want to look at a date, compare it to the record (row) previous and if the year is not consecutive then add in as many years to make it consecutive. eg. 23/06/2001, 04/12/2005 (2 rows) I would want to become 23/06/2001, 01/01/2002, 01/01/2003, 01/01/2004, 04/12/2005 (the program inserts 3 new rows). This would be done for each group only, so comparisons between last row of A and first of B are not made.
Apologies for asking here first, I could try a programmers forum but they are scarier than this place!
bump for lunch...
It's a long time since I did SAS - before they started using SQL actually.
[img] 
[/img]
....Rodney
geek 😛
What you need to do is sort the dates first into the order you want, i.e. by group. Get the year part of the date and create a 'Do' loop from the start year until you reach the next year, maybe something along the lines of
if start+1 < end then output new row
I'm trying...not clever enough though
EDIT - that was to Bigjim. Thanks Alan
You could sort and use a lag function,
Data date;
set orgdata;
if group =lag(group) then do;
if year(date) ne year(lag(date) then do;
date = '01JAN'||year(lag(date));
end;
end;
else output;
run;
or something like that depending on how the data set is structured or sorted.
brilliant, thank you. That should set me off on the right track.
Saying that lags and if's together not a good idea.
data dataset2;
format date date9.;
set dataset1;
by group date;
retain prevdate;
drop prevdate i;
if first.group then do;
prevdate = .;
output;
end;
else do;
output;
do i = year(prevdate) + 1 to year(date) - 1;
date = mdy(1, 1, i);
output;
end;
date = mdy(1, 1, i);
end;
prevdate = date;
run;