topic How do I create a new variable based on a defined date range? in SAS Studio

How do I create a new variable based on a defined date range?

gtjoeckel — Thu, 09 Apr 2020 06:08:42 GMT

Hello all,

I'm looking to create a new categorical variable CropYear from a predetermined range of dates in my ShipDate variable:

2016 - 08/09/2016 through 08/01/2017

2017 - 08/02/2017 through 07/29/2018

2018 - 07/30/2018 through 07/28/2019

I'm using SAS Studio, any coding help anyone can provide would be helpful and appreciated! I'm new to SAS.

Thanks,

Garrett

Re: How do I create a new variable based on a defined date range?

andreas_lds — Thu, 09 Apr 2020 06:15:35 GMT

Providing data in usable form is recommended and explaining the rules that have to be applied is essential to understand what you want. So please help us to help you.

Re: How do I create a new variable based on a defined date range?

Kurt_Bremser — Thu, 09 Apr 2020 06:24:40 GMT

How do you calculate the ranges, or do you have them in a lookup dataset?

Re: How do I create a new variable based on a defined date range?

Jagadishkatam — Thu, 09 Apr 2020 07:30:35 GMT

I am not sure of the requirement, but i assume you have a data as have and you need a want dataset as below, please provide more details if this does not meet your requirement

data have;
input shipdate :&$200.;
cards;
08/09/2016 through 08/01/2017
08/02/2017 through 07/29/2018
07/30/2018 through 07/28/2019
;

data want;
set have;
cropyear=scan(scan(shipdate,1,' '),3,'/');
run;

Re: How do I create a new variable based on a defined date range?

ballardw — Thu, 09 Apr 2020 14:24:54 GMT

BEST really is to provide an example of your actual existing data as you are not providing details that are likely very critical. Such as is your shipdate variable a Date value, character or something else.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

One of the more flexible ways, especially since the boundary values are not "nice" (as if first day of the same month) could be a custom format if your date is a SAS date value. The example below creates a format and a small example date set and a new character variable with the cropyear value. If your date is a SAS date value then you likely do not need to create a new variable just use the format with your existing variable. The only way to provide literal values for dates is the one shown with a quoted Date9. or Date7 format appearing value followed by D to tell SAS you want the date literal value to be used.

Proc format library=work;
value cropyear
'09Aug2016'd - '01Aug2017'd = '2016'
'01Aug2017'd - '29Jul2018'd = '2017'
'30Jul2018'd - '28Jul2019'd = '2018'
;
run;

data example;
   do date= '09Aug2016'd, '10Sep2016'd,'01Aug2017'd, '02Aug2018'd;
      cyear = put(date,cropyear.);
      output;
   end;
   format date mmddyy10.;
run;

proc print data=example;
run;

Re: How do I create a new variable based on a defined date range?

gtjoeckel — Thu, 09 Apr 2020 19:14:30 GMT

Thanks all, sorry I didn't include sufficient data and code to make it clearer. WIth ballardw post I was able to piece it together with the following code and make it work:

proc format library=work;
value cropyear
'09Aug2016'd - '01Aug2017'd = '2016'
'01Aug2017'd - '29Jul2018'd = '2017'
'30Jul2018'd - '28Jul2019'd = '2018';
run;

data capstone.galanewdata;
set capstone.galanewdata;
CropYear = put(ShipDate,cropyear.);
run;

proc sort data=work.test out=work.yearsort;
by CropYear descending ShipDate;
run;

proc sort data=work.yearsort nodupkey;
by CropYear;
run;

Here's last entry for each crop year:

Thanks,

Garrett