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

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

Kirt — Sun, 21 Sep 2014 17:35:51 GMT

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.

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

art297 — Sun, 21 Sep 2014 18:37:37 GMT

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;

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

jakarman — Sun, 21 Sep 2014 19:12:06 GMT

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)

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

art297 — Sun, 21 Sep 2014 23:10:53 GMT

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;

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

Kirt — Sun, 21 Sep 2014 23:11:24 GMT

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.

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

Kirt — Sun, 21 Sep 2014 23:25:10 GMT

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;

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

art297 — Mon, 22 Sep 2014 00:04:02 GMT

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).

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

Tom — Mon, 22 Sep 2014 00:14:24 GMT

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 fyear:) (=);

end;

format date yymmdd10. ;

keep date fyear:;

run;

proc calendar data=have ;

start date;

run;

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

jakarman — Mon, 22 Sep 2014 08:38:08 GMT

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