note: invalid argument to function qtr. missing values may be generated

[lupegon34]

hello all,

I am trying to convert some dates to quarters and week days as well as calculating length of stay with them using proc sql. I have used the code before and it's worked but today it's been giving me the following notes:

 

below is a sample of the code that I've been using and my formats:

proc sql; create table NEW as

select a.*,

      case when VAR='88'  then 1 else 0 end as CAT,

      put(qtr(VAR1), qr.) as a_qtr,     

     put(qtr(VAR2), qr.) as d_qtr,

      put(weekday(VAR1), wk.) as a_day,

      intck('DAY',VAR2,VAR1) as c_los,

from OLD a

;

quit;

value wk
1='SUN'
2='MON'
3='TUE'
4='WED'
5='THU'
6='FRI'
7='SAT'

value qr
1='Jan-Mar'
2='Apr-Jun'
3='Jul-Sep'
4='Oct-Dec'

What am I doing wrong??

[RW9]

And what does var1, var2 etc. contain?  Show some test data in the form of a datastep using the code window = {i} above post area and following this post if needed:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

The first two notes are telling you that var1 or var2 contain data which is missing or invalid, and hence a missing value is returned.

[lupegon34]

They are both numeric variables and contain dates with the date9. format applied to them.

So for example, I have 20160103 20160104 20160105. I want to be able to create quarters and specify what day of the week it is from these dates. Then I wanted to apply a specific format

[RW9]

Please post examples as requested.  If you really have numeric date variables and are passing those, then main check I would make is that there aren't observations with missing values, as missing value is invalid.

[Kurt_Bremser]

Testing always beats speculating:

data old;
var1 = today();
var2 = 123456789;
var3 = .;
run;

proc sql;
create table new1 as
select
  qtr(var1) as var1x
from old
;
create table new2 as
select
  qtr(var2) as var2x
from old
;
create table new3 as
select
  qtr(var3) as var3x
from old
;
quit;

Log from that:

24         data old;
25         var1 = today();
26         var2 = 123456789;
27         var3 = .;
28         run;

NOTE: The data set WORK.OLD has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

29         
30         proc sql;
31         create table new1 as
32         select
33           qtr(var1) as var1x
34         from old
35         ;
NOTE: Table WORK.NEW1 created, with 1 rows and 1 columns.

36         create table new2 as
37         select
38           qtr(var2) as var2x
39         from old
40         ;
NOTE: Invalid argument to function QTR. Missing values may be generated.
NOTE: Table WORK.NEW2 created, with 1 rows and 1 columns.

41         create table new3 as
42         select
43           qtr(var3) as var3x
44         from old
45         ;
2                                                          Das SAS System                             15:35 Friday, January 19, 2018

NOTE: Invalid (or missing) arguments to the QTR function have caused the function to return a missing value.
NOTE: Table WORK.NEW3 created, with 1 rows and 1 columns.

46         quit;

Note that the second value causes the same message you encountered, so you have a numeric value that is outside of the range of valid values for SAS dates. Are you sure it isn't a datetime value instead of a date?

[Kurt_Bremser · Accepted solution]

---

@lupegon34 wrote:

They are both numeric variables and contain dates with the date9. format applied to them.

So for example, I have 20160103 20160104 20160105. I want to be able to create quarters and specify what day of the week it is from these dates. Then I wanted to apply a specific format

---

Ouch. A numeric value of 20160103 is not a SAS date value. If it was a SAS date value formatted with date9., it would display as 03jan2016.

But the raw value 20160103 would cause exactly your error message when used in a date-related function (see my other answer).

[lupegon34]

You know, shortly after I posted that I was like "wait a minute .....". lol.

Thank you!

Apparently all the date variables had been incorrectly formatted due to my company moving over to a new database. So of course, now everything has to be reformatted. *face palm*

I appreciate your help!

[ballardw]

---

@lupegon34 wrote:

They are both numeric variables and contain dates with the date9. format applied to them.

So for example, I have 20160103 20160104 20160105. I want to be able to create quarters and specify what day of the week it is from these dates. Then I wanted to apply a specific format

---

If you have values that look like 20160103 what value do you get when you apply date9. format?

If you get something similar to this for YOUR values then you have simple numeric values that are not SAS date values.

1    data _null_;
2       x=20160103;
3       put x= date9.;
4    run;

x=*********

The above ****** is because that value is not recognizable as a date to SAS. The current range of values that the date functions will accept ends at 31Dec20000 (yes year 20,000) which corresponds to a simple numeric value (number of days since 1JAN1960) of 6589335. Your 20160103 is about 135570768 days past the end of valid date values.

Likely to get an actual SAS date valued variable that will actually work you need to read that appropriately with something like

data _null_;
   x=20160103;
   sasdate= input(put(x,8.),yymmdd8.);
   put sasdate date9.;

run;

And the formats or functions will work on the sasdate variable constructed as above.

[lupegon34]

Thank you so much!

[Kurt_Bremser]

These functions expect SAS date values as arguments; these are numeric and contain the count of days since 1960-01-01.