Why is it impossible to compare string and date with yyq4. format?

[seohyeonjeong]

Hi everyone,

I'm a beginner of SAS program.

I want to compare the value of string and date with yyq4. format

I subtract the string value YYQn from ID_loan.

 * YYQn = origination year and quarter

I also get Loan origination date named DT_orig from the following formula.

* Loan_age = ((Monthly Reporting Period) - Loan Origination Date(MM/YY)) - 1 month

I hope I can extract the value which DT_orig and YYQn is 

For example, the data which has DT_orig = 20140101 should have YYQn=14Q1

if the DT_orig and YYQn does not match, I have to extract those rows.

Here is the same of data and my code which doesn't work.

sample_2014_2017

DATA sample_2014_2017;
set sample_svcg_2014to2017;
Period_YM = input(put(Period, best6.),yymmn6.);
DT_orig_YM = intnx('month', Period_YM, -loan_age-1);
DT_orig = input(put(DT_orig_YM, yymmn6.),best6.);
format Period_YM yymmn6. DT_orig_YM yymmn6.;
keep ID_loan Period Period_YM DT_orig DT_orig_YM;
RUN;

DATA temp01;
	set sample_2014_2017;
	YYQn = input(substr(ID_loan,3,4), yyq4.); 
	Dt_orig_qt = Dt_orig_YM;
	format Dt_orig_qt YYQ4. YYQn YYQ4.;
	keep ID_loan YYQn Dt_orig_qt; 
RUN;

DATA temp02;
	set temp01;
	if input(Dt_orig_qt, best4.) != input(YYQn, best4.);
RUN;

Thank you in advance.

[Oligolas]

Hi,

check your "not equal" operator.

In SAS it is written "NE" or "^="

[seohyeonjeong]

Thank you for a comment.

That was a mistake, and "not equal" operator is not a problem here.

[Kurt_Bremser]

Maxim 2: Read Your Log.

 69         data test;
 70         set sashelp.class;
 71         if sex != "M";
                    _
                    22
 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: ein Name, eine Zeichenkette in Hochkommata, 
               eine numerische Konstante, eine Datetime-Konstante, ein fehlender Wert, INPUT, PUT.  
 
 72         run;

Your "not equals" operator is invalid in SAS.

[Kurt_Bremser]

Please supply example data in usable form (in a data step with datalines), so we can see the exact source values, and all the variable attributes, especially the assigned formats.

We cannot use pictures in developing and testing code.

[Tom]

Why do some of the columns in your photograph have different icons next to them?

Two have blue circles and two have squares.

[Tom]

You seem to have trouble telling the difference between formats like BEST6. and YYMMN6. and informats like 6. and YYMM6.

YYMMN is a FORMAT you can use to display a date with only year and month numbers without any divider.

YYMM is an INFORMAT you can use to read date strings that only contain year and month numbers.  It will work on string with and without dividers.

BEST is a format that can determine the best way to display a number in a limited number of characters.  If you use BEST as an INFORMAT is it just an alias for the normal numeric informat since the concept of a "best" way to store a number does not make any sense.

Period_YM = input(put(Period, Z6.),yymm6.)

[Kurt_Bremser]

I also recommend to study this:

Working with Dates in the SAS System 

so you get to know what a valid date value is, and what not.

Because of

YYQn = input(substr(ID_loan,3,4), yyq4.);

YYQn is a valid SAS date value, a number containing the days from 1960-01-01.

Because of this:

DT_orig_YM = intnx('month', Period_YM, -loan_age-1);

DT_orig_YM is also a valid SAS date value, so Dt_orig_qt, created here:

Dt_orig_qt = Dt_orig_YM;

is also a numeric value.

But here:

if input(Dt_orig_qt, best4.) != input(YYQn, best4.);

you use the INPUT function for both variables, which expects a character value as its first argument. So you first force SAS to convert both values to strings, after which you use two different informats to read those strings. But since SAS uses (per default) the BEST12. format (right-aligned) to convert numbers to strings, and you only read the first 4 bytes of that 12-byte string (which are all blanks, because dates before 2233-10-15 have only 5 or less digits), you only get missing values. One missing value is equal to another missing value.

If you want to find out if two dates fall into the same quarter, align them both to the start (or end) of the quarter with the INTNX function, and compare them directly.

Simple example code:

data test;
input (date1 date2) (:yymmdd10.);
format date1 date2 yyq6.; /* always use 4-digit years! */
if intnx('quarter',date1,0,"b") = intnx("quarter",date2,0,"b")
then check = "same";
else check = "diff";
datalines;
2022-06-15 2022-06-20
2022-06-15 2022-07-04
;

[ballardw]

Let's pull out a few pieces of your problem and examine what happens with one of your Id_loan values.

data example;
   ID_loan='F114Q1000005';
	YYQn = input(substr(ID_loan,3,4), yyq4.);
   put yyqn=;
   z= input(YYQn, best4.);
   z2= input(put(yyqn,5.),best4.);

run;

If you look at the outpu yyqn is 5 digits. Z is missing missing because the default automatic conversion of a numeric to character uses a best12. format that is left justifired, so it "inputs" 4 blanks. Your Input(yyqn,best4.) should have your LOG show something like this which is always flag that you may have a problem.

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).

Second, the value of YYQn as a numeric is 5 digits in that year range, so reading it back in with a Best4. (sic) informat would truncate the value to 4 digits. IF you convert the number to a string correctly then that is demonstrated in the Z2 is show above.

You repeat the exact same Input conversion error on the variable Dt_orig_qt which would also have a conversion note.