I am looking for a solution to increment by 10 from the first date to end date.
In the table below the start date for reading_date is 12/17/2019, what I am trying to do is to create a loop for this date starting with 12/17/2019 by 10 and end in 1/27/2020. After the date is created I need to add the rest of the data corresponding to this date from the original table (please see "want")
table
t1.reading_date | t1.use_date | t1.name | t1.reading_pct |
12/17/2019 | 12/11/2019 | file1 | 75.00915527 |
12/22/2019 | 12/11/2019 | file1 | 75.5859375 |
12/27/2019 | 12/11/2019 | file1 | 76.90429688 |
1/1/2020 | 12/11/2019 | file1 | 74.29199219 |
1/2/2020 | 12/11/2019 | file1 | 64.93835449 |
1/7/2020 | 12/11/2019 | file1 | 65.10620117 |
1/12/2020 | 12/11/2019 | file1 | 66.90063477 |
1/17/2020 | 12/11/2019 | file1 | 66.47033691 |
1/22/2020 | 12/11/2019 | file1 | 66.35131836 |
1/27/2020 | 12/11/2019 | file1 | 59.61303711 |
want | |||
new(t1.reading_date) | t1.use_date | t1.name | t1.reading_pct |
12/17/2019 | 12/11/2019 | file1 | 75.00915527 |
12/27/2019 | 12/11/2019 | file1 | 76.90429688 |
1/7/2020 | 12/11/2019 | file1 | 65.10620117 |
1/17/2020 | 12/11/2019 | file1 | 66.47033691 |
1/27/2020 | 12/11/2019 | file1 | 59.61303711 |
Any ideas?
It's so easy in C++, I don't know how to generate this date in SAS?
I have exhausted all my options. I really need your help!
Thank you.
data have;
infile cards expandtabs truncover;
input (reading_date use_date) (:mmddyy12.) name $ reading_pct ;
format reading_date use_date mmddyy10.;
cards;
12/17/2019 12/11/2019 file1 75.00915527
12/22/2019 12/11/2019 file1 75.5859375
12/27/2019 12/11/2019 file1 76.90429688
1/1/2020 12/11/2019 file1 74.29199219
1/2/2020 12/11/2019 file1 64.93835449
1/7/2020 12/11/2019 file1 65.10620117
1/12/2020 12/11/2019 file1 66.90063477
1/17/2020 12/11/2019 file1 66.47033691
1/22/2020 12/11/2019 file1 66.35131836
1/27/2020 12/11/2019 file1 59.61303711
;
data want;
set have;
retain temp;
if temp<=reading_date then do;
output;temp=reading_date;temp+10;
end;
drop temp;
run;
I suppose your input is a sas data set.
I understand you want to subset from the input those lines of incremented by 10 days from start date.
Should it be done per t1.name or any other ID not shown in your post?
then try next code:
data want;
set have;
by name;
retain next_date; drop next_date;
if first.date then do;
next_date = reading_date +10;
output;
end; else
if reading_date = next_date then do;
next_date +10;
output;
end;
run;
Thank you Shmuel,
yes the data is in SAS, I know the logic of the code, but it is not working very well for me.\
My fields are dates, and I cannot get it to work properly. I think I am more struggling with the syntax than logic.
Do you have a code to share for dates?
Thank you again.
The start date and end date is not the issue.
I can collect them based on min and max of the date for same id.
@Claudia_SAS wrote:
The start date and end date is not the issue.
I can collect them based on min and max of the date for same id.
Please note that your example data does not have a column named id.
Please post correct example data in a data step with datalines, and the expected output from exactly this dataset, so we have a test case.
You can make it much easier for us to diagnose your code by posting it. Use the "little running man" right next to the icon indicated for posting SAS code.
How do you determine the start of a sequence? Or does a sequence always cover 20 days?
I.e. from where do you know that 2020-01-07 is a new start, but not the other dates in between? And from where do you know that 2019-12-27 is the last entry of the first sequence?
data have;
infile cards expandtabs truncover;
input (reading_date use_date) (:mmddyy12.) name $ reading_pct ;
format reading_date use_date mmddyy10.;
cards;
12/17/2019 12/11/2019 file1 75.00915527
12/22/2019 12/11/2019 file1 75.5859375
12/27/2019 12/11/2019 file1 76.90429688
1/1/2020 12/11/2019 file1 74.29199219
1/2/2020 12/11/2019 file1 64.93835449
1/7/2020 12/11/2019 file1 65.10620117
1/12/2020 12/11/2019 file1 66.90063477
1/17/2020 12/11/2019 file1 66.47033691
1/22/2020 12/11/2019 file1 66.35131836
1/27/2020 12/11/2019 file1 59.61303711
;
data want;
set have;
retain temp;
if temp<=reading_date then do;
output;temp=reading_date;temp+10;
end;
drop temp;
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.