Good Morning,
I am trying to find out if a license was valid between a date range. I have and id number of the license, begin date and the expiration date of the licenses. The date range is 11/19/2017-11/19/2019. I can do one year without much of an issue by looking for licenses that expired within that range or that the begin date fell within it. The catch is that some of the licenses are valid for 5 years and when using that logic may not be captured in the results.
Wait, I think I see where you want to go. See this:
data have;
input id $ (startDate expireDate) (:yymmdd10.);
format startDate expireDate yymmddd10.;
datalines;
123456 2017-11-19 2018-11-19
123565 2015-07-23 2020-07-23
995874 2017-11-16 2018-11-16
986632 2005-01-08 2099-01-08
888888 2010-01-01 2016-12-31
999999 2019-11-20 2029-11-20
;
%let period_start=%sysfunc(inputn(2017-11-19,yymmdd10.));
%let period_end=%sysfunc(inputn(2019-11-19,yymmdd10.));
data want;
set have;
flag = (startdate <= &period_end and expiredate >= &period_start);
run;
I added two cases that fall completely out of the period, to force a "false" flag.
Please supply representative example data in usable form, e.g.
data have;
input id $ (begdate enddate) (:yymmdd10.);
format begdate enddate yymmddd10.;
datalines;
123456 2017-11-19 2019-11-19
;
Sorry, I'm still a bit new to SAS. you almost got the fields named correctly too. The 5 years matter because someone can start before the first date that I want to know if its valid and expire after, I just don't know how to capture them, but while getting this small data set together i realized that I could just separate the 5 year licenses just pull those that were purchased between 11/19/2012-11/19/2019 and that would accomplish the same thing. I could also be explaining this poorly.
but here is a sample of the data set.
data have;
input id $ (startDate expireDate) (:yymmdd10.);
format startDate expireDate yymmddd10.;
datalines;
123456 2017-11-19 2018-11-19
123565 2015-07-23 2020-07-23
995874 2017-11-16 2018-11-16
986632 2005-01-08 2099-01-08
;
And what do you expect to get out of this example dataset?
Wait, I think I see where you want to go. See this:
data have;
input id $ (startDate expireDate) (:yymmdd10.);
format startDate expireDate yymmddd10.;
datalines;
123456 2017-11-19 2018-11-19
123565 2015-07-23 2020-07-23
995874 2017-11-16 2018-11-16
986632 2005-01-08 2099-01-08
888888 2010-01-01 2016-12-31
999999 2019-11-20 2029-11-20
;
%let period_start=%sysfunc(inputn(2017-11-19,yymmdd10.));
%let period_end=%sysfunc(inputn(2019-11-19,yymmdd10.));
data want;
set have;
flag = (startdate <= &period_end and expiredate >= &period_start);
run;
I added two cases that fall completely out of the period, to force a "false" flag.
That worked! I am still reading and learning the SAS base programming book, but after reading about some of the commands you had it all makes sense. Its just knowing what options I have to make things easier. Thank you for your help!
If you have start and end dates why does the 5 years matter?
How do you know if a license is 1 year or 5 year?
Is your data ll in the same data set or different data sets? Does the range change or is it fixed? Are your variables SAS dates?
Can all rows be handled in the same manner or do you need to do this over years/groups?
Most would likely be answered with sample data.
Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.