New to SAS and I'm Trying to switch Programs from VBA and am Stuck

Reply
New Contributor
Posts: 3

New to SAS and I'm Trying to switch Programs from VBA and am Stuck

I'm trying to convert my MS Access databases to SAS.  The first stumbling block I ran into was calculating years (or any date intervals). There are a lot of SAS functions for dates that work for the majority of users.  Unfortunately, I need special intervals that do not work with these function.  The beginning or ending dates for a year (any interval for that matter, month, or quarter) are based on the number of days that are in that week.  For example, I will count all dates between 12/29/2013 and 1/2/2015 as occurring in 2014.   Each week begins on a Sunday and ends on Saturday- common.  However, For 2014, because there are only two days following 12/29/2013, we count the two days after the 29th in 2014.  Thus, whenever an interval contains four or more days, it belongs to that interval.  Thus using something like variable2 = Year(Date Variable) will count records on 12/30/2013 or 1/2/2015 in the wrong year.

Here is what I've tried in SAS:

The existing date in my database is Edate which is imported as a date.

I define a variable like EYear.

Then I've tried to assign Edate to EYear with and If and Else If statements like (tried with and with out quotes etc.):

If Edate >= 12/29/2013 and Edate <= 01/02/2015 then EYear = 2014;  <--Doesn't work and has me baffled and it's probably something simple.

Coming from a VB background I would program it something like:

Select Case Edate

     Case <= '12/31/2011'

        Eyear = 2011

     Case <= '12/29/2012'

        Eyear = 2012

     Case <= '12/28/2013'

        Eyear = 2013

     Case <= '1/2/2015'

        Eyear = 2014

End Select

Any help would be greatly appreciated.  Thank you in advance.

PROC Star
Posts: 7,433

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

Kirt,

Yes SAS also has a select statement, but you have to indicate dates as being SAS dates. Here is an example of how you could do it with a select statement, but also an example of how you could do it more easily using the year function:

data have;

  input edate date9.;

  format edate date9.;

  cards;

10oct2014

6jun2013

5may2012

2feb2011

;

data want;

  set have;

  Select;

     when (edate <= '31dec2011'd)

        Eyear = 2011;

     when (edate <= '31dec2012'd)

        Eyear = 2012;

     when (edate <= '31dec2013'd)

        Eyear = 2013;

     when (edate <= '31dec2014'd)

        Eyear = 2014;

     otherwise;

  end;

run;

data want;

  set have;

  eyear=year(edate);

run;

New Contributor
Posts: 3

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

Thanks Jaap.  The select approach worked.  The more efficient doesn't work because those records with edate in preceeding year or coming year get counted wrong.  Unless I'm interpreting something wrong.  Thanks for the select approach.  Smiley Happy

New Contributor
Posts: 3

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

Thanks Aurthur.  I put Jaap.  I'm looking at Jaap's recommendation and your additional code.  I do not understand the intnx and have looked at the documentation.  I do not know how the different year start and end dates would work in this function.  I see lots of syntax and examples that are not similar with this function. Although the select works, I have a lot more variables and time frames to code and something more preferable would be nice.

This is the select statement that worked (went back to file read and did the format date9. on the input read.:

Select;

     when (DtEpisode <= '31dec2011'd)

        Eyear = 2011;

     when (DtEpisode <= '29dec2012'd)

        Eyear = 2012;

     when (DtEpisode <= '28dec2013'd)

        Eyear = 2013;

     when (DtEpisode <= '2Jan2015'd)

        Eyear = 2014;

     otherwise;

  end;

PROC Star
Posts: 7,433

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

Kirt,

My 2nd set of suggested code (i.e.,

data want;

  set have;

  eyear=year(intnx('YEARV',edate,0,'M'));

run;

Simply identifies the year at the midpoint of the iso year. While I can't test it, I think it will provide the same answer as the select approach (but with less code and applicable for all years).

Valued Guide
Posts: 3,208

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

There are al lot of date/time conventions and intervals with SAS indeed. That much you can look for the standards that are implemented instead of programming manually.
You logic seem to me as:  Retail Calendar Intervals: ISO 8601 Compliant  SAS(R) 9.4 Language Reference: Concepts, Third Edition (About Date and Time Intervals)

---->-- ja karman --<-----
PROC Star
Posts: 7,433

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

I can't test this at the moment but, if is correct that you are trying to get ISO 8601 compliant years, then something like the following should work (I'd think):

data want;

  set have;

  eyear=year(intnx('YEARV',edate,0,'M'));

run;

Super User
Super User
Posts: 6,844

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

To get the weeks to start on Sunday instead of Monday you can add one to the date in your formula.

Try this little bit of code to see the difference.

data have ;

  do year=2011 to 2015 ;

  do date=mdy(12,24,year) to mdy(1,7,year+1) ;

    fyearM=year(intnx('YEARV',date,0,'M'));

    fyearS=year(intnx('YEARV',date+1,0,'M'));

    output ;

    if fyearM ne fyearS then put (date fyearSmiley Happy (=);

  end;

  end;

  format date yymmdd10. ;

  keep date fyear:;

run;

proc calendar data=have ;

  start date;

run;

Valued Guide
Posts: 3,208

Re: New to SAS and I'm Trying to switch Programs from VBA and am Stuck

Kirt, You are missing some concepts, let me try.

Time is a measurement and getting that to some precision is a more nasty topic as being suspected.

Counting the days is the most easy one. If you do that on a local common position en our globe it will give you integer numbers.

That is what some systems like SAS are doing, counting the day-s. The point of zero with sas is chosen as 1-jan-1960:0:0:0.0 . It is not the AD-BC point of zero having the advantage of working with small numbers.

mind-switch 01:

Each number can be represented in a date as string, no matter how you like to write that or what calendar convention you would like to use. 

The SAS formats are doing this perfectly.

You are not working on strings for dates with SAS as a lot of people tend to do in Cobol (VB) a RDBMS or other language.

A year (Gregorian calendar) has 365 of 366 days, does start at 1 jan. It was once started at 1 mar,  December  being the 10-th month not the 12-th. (December is ten in latin). having 12 months an 52 or 53 weeks.

The weeknumber was for a long time not standardized with the effect you could have different ones being used. With the iso8601 this became standardize. Of course you can still use your own definitions. Iso is starting with mo as first day of the week, churches are sometimes starting with Su as the the first one. To understand each other there must some agreement on that.

The weekv type is the iso8601 implementation. You can find weeku weekw types they are slightly different.  SAS(R) 9.4 Formats and Informats: Reference

mind-switch 02:

As dates are just numbers you can define intervals with those numbers, having a starting number and an ending number. You can do this programmatically or using functions perhaps the hyping word is using the api-s .

SAS has several of those one of the classic ones is "year( )" just giving the year as number auto of that date-number.

More advanced ones are intnx (shift in time) and intck (calacuate difference) SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition.

mind-switch 03:

There are a lot of procedures in SAS than can work on the formatted values, this avoids a necessary data-conversion.

This concept on working on formatted values that you apply as segregated object is rather unique as language concept. It is only seen in languages paste 3GL (4 or 5GL). 

.....

I made a demo program that will show some of the effects.

- Generating test-dates is counting start/end by some intervals, remember it are numbers.

- aligning a date tot the start or begin of an interval is using a increment of zero.

  By this it is possible to pre-define a list of intervals as of some reference date.

- the calendar year is a calculated one  

- putting this all in some formatted value output in a human readable way (using that put-s).  It shows:

  + the number data conversion

  + the weekv. long type notation.  See the weeknumbers and years are running into the next year
  + the weeku notation with those numbers of 0 and the differnce su/mo

  + year and the begin/end interval dates

  + formatted shortend weekv value. This is leaving out the day-number and leaving it as showing up yyWww and that yy is not necessary equal at the calendar year.

     remember this one did not change the date number value just showing it as a representation of that.
.....

Test program:

data dates;

do datetst='28dec2009'd to '6jan2010'd; output ; end;
do datetst='28dec2010'd to '6jan2011'd; output ; end;
do datetst='28dec2011'd to '6jan2012'd; output ; end;
do datetst='28dec2012'd to '6jan2013'd; output ; end;
do datetst='28dec2013'd to '6jan2014'd; output ; end;
do datetst='28dec2014'd to '6jan2015'd; output ; end;
do datetst='28dec2016'd to '6jan2017'd; output ; end;

run;

options ls=150;
data datecnv;
  set dates ;
  datefr=intnx('weekv',datetst,0,'B');
  datelr=intnx('weekv',datetst,0,'E' );
  cyear=Year(datetst);
  put "dateno:" datetst "  " datetst date. "  dateisowk:" datetst weekv. "  date wu" datetst weeku.
  " -- year: " cyear " -- period begin:" datefr date. " end: " datelr date.
  ".. formatted short " datetst weekv6.  ;
run;

Results:

dateno:18259   28DEC09  dateisowk:2009-W53-01  date wu2009-W52-02 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18260   29DEC09  dateisowk:2009-W53-02  date wu2009-W52-03 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18261   30DEC09  dateisowk:2009-W53-03  date wu2009-W52-04 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18262   31DEC09  dateisowk:2009-W53-04  date wu2009-W52-05 -- year: 2009  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18263   01JAN10  dateisowk:2009-W53-05  date wu2010-W00-06 -- year: 2010  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18264   02JAN10  dateisowk:2009-W53-06  date wu2010-W00-07 -- year: 2010  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18265   03JAN10  dateisowk:2009-W53-07  date wu2010-W01-01 -- year: 2010  -- period begin:28DEC09 end: 03JAN10.. formatted short 09W53

dateno:18266   04JAN10  dateisowk:2010-W01-01  date wu2010-W01-02 -- year: 2010  -- period begin:04JAN10 end: 10JAN10.. formatted short 10W01

dateno:18267   05JAN10  dateisowk:2010-W01-02  date wu2010-W01-03 -- year: 2010  -- period begin:04JAN10 end: 10JAN10.. formatted short 10W01

dateno:18268   06JAN10  dateisowk:2010-W01-03  date wu2010-W01-04 -- year: 2010  -- period begin:04JAN10 end: 10JAN10.. formatted short 10W01

dateno:18624   28DEC10  dateisowk:2010-W52-02  date wu2010-W52-03 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18625   29DEC10  dateisowk:2010-W52-03  date wu2010-W52-04 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18626   30DEC10  dateisowk:2010-W52-04  date wu2010-W52-05 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18627   31DEC10  dateisowk:2010-W52-05  date wu2010-W52-06 -- year: 2010  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18628   01JAN11  dateisowk:2010-W52-06  date wu2011-W00-07 -- year: 2011  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18629   02JAN11  dateisowk:2010-W52-07  date wu2011-W01-01 -- year: 2011  -- period begin:27DEC10 end: 02JAN11.. formatted short 10W52

dateno:18630   03JAN11  dateisowk:2011-W01-01  date wu2011-W01-02 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18631   04JAN11  dateisowk:2011-W01-02  date wu2011-W01-03 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18632   05JAN11  dateisowk:2011-W01-03  date wu2011-W01-04 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18633   06JAN11  dateisowk:2011-W01-04  date wu2011-W01-05 -- year: 2011  -- period begin:03JAN11 end: 09JAN11.. formatted short 11W01

dateno:18989   28DEC11  dateisowk:2011-W52-03  date wu2011-W52-04 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18990   29DEC11  dateisowk:2011-W52-04  date wu2011-W52-05 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18991   30DEC11  dateisowk:2011-W52-05  date wu2011-W52-06 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18992   31DEC11  dateisowk:2011-W52-06  date wu2011-W52-07 -- year: 2011  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18993   01JAN12  dateisowk:2011-W52-07  date wu2012-W01-01 -- year: 2012  -- period begin:26DEC11 end: 01JAN12.. formatted short 11W52

dateno:18994   02JAN12  dateisowk:2012-W01-01  date wu2012-W01-02 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18995   03JAN12  dateisowk:2012-W01-02  date wu2012-W01-03 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18996   04JAN12  dateisowk:2012-W01-03  date wu2012-W01-04 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18997   05JAN12  dateisowk:2012-W01-04  date wu2012-W01-05 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:18998   06JAN12  dateisowk:2012-W01-05  date wu2012-W01-06 -- year: 2012  -- period begin:02JAN12 end: 08JAN12.. formatted short 12W01

dateno:19355   28DEC12  dateisowk:2012-W52-05  date wu2012-W52-06 -- year: 2012  -- period begin:24DEC12 end: 30DEC12.. formatted short 12W52

dateno:19356   29DEC12  dateisowk:2012-W52-06  date wu2012-W52-07 -- year: 2012  -- period begin:24DEC12 end: 30DEC12.. formatted short 12W52

dateno:19357   30DEC12  dateisowk:2012-W52-07  date wu2012-W53-01 -- year: 2012  -- period begin:24DEC12 end: 30DEC12.. formatted short 12W52

dateno:19358   31DEC12  dateisowk:2013-W01-01  date wu2012-W53-02 -- year: 2012  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19359   01JAN13  dateisowk:2013-W01-02  date wu2013-W00-03 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19360   02JAN13  dateisowk:2013-W01-03  date wu2013-W00-04 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19361   03JAN13  dateisowk:2013-W01-04  date wu2013-W00-05 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19362   04JAN13  dateisowk:2013-W01-05  date wu2013-W00-06 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19363   05JAN13  dateisowk:2013-W01-06  date wu2013-W00-07 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19364   06JAN13  dateisowk:2013-W01-07  date wu2013-W01-01 -- year: 2013  -- period begin:31DEC12 end: 06JAN13.. formatted short 13W01

dateno:19720   28DEC13  dateisowk:2013-W52-06  date wu2013-W51-07 -- year: 2013  -- period begin:23DEC13 end: 29DEC13.. formatted short 13W52

dateno:19721   29DEC13  dateisowk:2013-W52-07  date wu2013-W52-01 -- year: 2013  -- period begin:23DEC13 end: 29DEC13.. formatted short 13W52

dateno:19722   30DEC13  dateisowk:2014-W01-01  date wu2013-W52-02 -- year: 2013  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19723   31DEC13  dateisowk:2014-W01-02  date wu2013-W52-03 -- year: 2013  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19724   01JAN14  dateisowk:2014-W01-03  date wu2014-W00-04 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19725   02JAN14  dateisowk:2014-W01-04  date wu2014-W00-05 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19726   03JAN14  dateisowk:2014-W01-05  date wu2014-W00-06 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19727   04JAN14  dateisowk:2014-W01-06  date wu2014-W00-07 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19728   05JAN14  dateisowk:2014-W01-07  date wu2014-W01-01 -- year: 2014  -- period begin:30DEC13 end: 05JAN14.. formatted short 14W01

dateno:19729   06JAN14  dateisowk:2014-W02-01  date wu2014-W01-02 -- year: 2014  -- period begin:06JAN14 end: 12JAN14.. formatted short 14W02

dateno:20085   28DEC14  dateisowk:2014-W52-07  date wu2014-W52-01 -- year: 2014  -- period begin:22DEC14 end: 28DEC14.. formatted short 14W52

dateno:20086   29DEC14  dateisowk:2015-W01-01  date wu2014-W52-02 -- year: 2014  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20087   30DEC14  dateisowk:2015-W01-02  date wu2014-W52-03 -- year: 2014  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20088   31DEC14  dateisowk:2015-W01-03  date wu2014-W52-04 -- year: 2014  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20089   01JAN15  dateisowk:2015-W01-04  date wu2015-W00-05 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20090   02JAN15  dateisowk:2015-W01-05  date wu2015-W00-06 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20091   03JAN15  dateisowk:2015-W01-06  date wu2015-W00-07 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20092   04JAN15  dateisowk:2015-W01-07  date wu2015-W01-01 -- year: 2015  -- period begin:29DEC14 end: 04JAN15.. formatted short 15W01

dateno:20093   05JAN15  dateisowk:2015-W02-01  date wu2015-W01-02 -- year: 2015  -- period begin:05JAN15 end: 11JAN15.. formatted short 15W02

dateno:20094   06JAN15  dateisowk:2015-W02-02  date wu2015-W01-03 -- year: 2015  -- period begin:05JAN15 end: 11JAN15.. formatted short 15W02

dateno:20450   28DEC15  dateisowk:2015-W53-01  date wu2015-W52-02 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20451   29DEC15  dateisowk:2015-W53-02  date wu2015-W52-03 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20452   30DEC15  dateisowk:2015-W53-03  date wu2015-W52-04 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20453   31DEC15  dateisowk:2015-W53-04  date wu2015-W52-05 -- year: 2015  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20454   01JAN16  dateisowk:2015-W53-05  date wu2016-W00-06 -- year: 2016  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20455   02JAN16  dateisowk:2015-W53-06  date wu2016-W00-07 -- year: 2016  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20456   03JAN16  dateisowk:2015-W53-07  date wu2016-W01-01 -- year: 2016  -- period begin:28DEC15 end: 03JAN16.. formatted short 15W53

dateno:20457   04JAN16  dateisowk:2016-W01-01  date wu2016-W01-02 -- year: 2016  -- period begin:04JAN16 end: 10JAN16.. formatted short 16W01

dateno:20458   05JAN16  dateisowk:2016-W01-02  date wu2016-W01-03 -- year: 2016  -- period begin:04JAN16 end: 10JAN16.. formatted short 16W01

dateno:20459   06JAN16  dateisowk:2016-W01-03  date wu2016-W01-04 -- year: 2016  -- period begin:04JAN16 end: 10JAN16.. formatted short 16W01

dateno:20816   28DEC16  dateisowk:2016-W52-03  date wu2016-W52-04 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20817   29DEC16  dateisowk:2016-W52-04  date wu2016-W52-05 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20818   30DEC16  dateisowk:2016-W52-05  date wu2016-W52-06 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20819   31DEC16  dateisowk:2016-W52-06  date wu2016-W52-07 -- year: 2016  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20820   01JAN17  dateisowk:2016-W52-07  date wu2017-W01-01 -- year: 2017  -- period begin:26DEC16 end: 01JAN17.. formatted short 16W52

dateno:20821   02JAN17  dateisowk:2017-W01-01  date wu2017-W01-02 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20822   03JAN17  dateisowk:2017-W01-02  date wu2017-W01-03 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20823   04JAN17  dateisowk:2017-W01-03  date wu2017-W01-04 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20824   05JAN17  dateisowk:2017-W01-04  date wu2017-W01-05 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

dateno:20825   06JAN17  dateisowk:2017-W01-05  date wu2017-W01-06 -- year: 2017  -- period begin:02JAN17 end: 08JAN17.. formatted short 17W01

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 8 replies
  • 415 views
  • 3 likes
  • 4 in conversation