Problem with SUBSTR date

[Gieorgie]

I need to extract the date using a subtr from 2021-09-11 to 2021-09

	
create table PolisyEnd as 
    select distinct       
    (t4.spr_NRB) as NRB
	,datepart(t1.PRP_END_DATE) as POLICY_VINTAGE format yymmdd10.,

when im trying use substr

,input(substr(datepart(t1.PRP_END_DATE,1,4))||'-'||substr(datepart(t1.PRP_END_DATE,5,2))||'-'||substr(datepart(t1.PRP_END_DATE,7,2)),yymmdd10.) 

I got error

"WARNING: Function DATEPART requires at most 1 argument(s). The extra one(s) will be ignored.
ERROR: Function SUBSTR requires at least 2 argument(s).
WARNING: Function DATEPART requires at most 1 argument(s). The extra one(s) will be ignored.
ERROR: Function SUBSTR requires at least 2 argument(s).
WARNING: Function DATEPART requires at most 1 argument(s). The extra one(s) will be ignored.
ERROR: Function SUBSTR requires at least 2 argument(s)."

Is there chance to repair ?

[PeterClemmensen]

Looks like you misplaced a few closing parentheses. 

I think this works

,input(substr(datepart(t1.PRP_END_DATE),1,4)||'-'||substr(datepart(t1.PRP_END_DATE),5,2)||'-'||substr(datepart(t1.PRP_END_DATE),7,2),yymmdd10.)

[Gieorgie]

Its not helped same error shows

[AMSAS]

What didn't work, please provide the full log.
It is also helpful if you:

Provide some sample data
Build up your functions/code little by little testing as you go. For example if you want to do something like function1(function2(function3,1,3),"x")) then start with just function3 and get that working first before adding function2 and finally function1.

[AMSAS]

What are you attempting to do, because as I look at your code it doesn't make sense to take the datepart of a SAS Date/Time value and then split it into a string.
The datepart is just going to be the number of days since 01JAN1960, a meaningless number to us humans

[Patrick]

Below syntax is working - right?

datepart(t1.PRP_END_DATE) as POLICY_VINTAGE format yymmdd10.

If so then PRP_END_DATE is a SAS DateTime value stored in a numerical variable as the count of seconds since 1/1/1960. You certainly can't use a string function like substr() for such a value.

What problem do you actually want to solve given that your initial syntax appears to work already?

[Gieorgie]

I would like to cut a day from 2022-07-23, so that only the year and month 2022-07 remain. He then needs to count the number for each year and group one record for each month

[Patrick]

When you look at your data then you see a date string - but that's just because the format you've chosen prints the internal SAS Date value in a human readable form.

A SAS datetime value is the count of seconds since 1/1/1960, the datepart() function converts the value to a SAS date value which is the count of days since 1/1/1960. 

It is very important that you understand SAS Date and Datetime values and if you don't then read-up in the SAS docu.

It's very beneficial to store and work with dates as SAS date and datetime values as compared to working with dates stored as text.

"needs to count the number for each year and group one record for each month"

I don't fully understand what you're trying to do but try to understand below code. That should you give all the pointers.

If you want us to provide you "real" code then you need to provide representative sample data for us (in the form of a SAS data step creating such data) and then also show the desired result.

data have;
  format prp_end_date datetime21.;
  do i=-1000 to 0 by 1;
    prp_end_date=intnx('dtday',datetime(),i,'s');
    output;
  end;
run;

proc sql;
select
  intnx('month',datepart(PRP_END_DATE),0,'b') as date_first_of_month format yymon7.,
  count(i) as cnt_i
from have
group by date_first_of_month
;
quit;

[Gieorgie]

Thanks ! i resolved this i changed data to string ,put(datepart(t1.PRP_END_DATE),yymmd7.) as POLICY_VINTAGE,

[Kurt_Bremser · Accepted solution]

DO NOT DO THIS.

Your new variable is still a date-related value, so store it as a date, as it enables you to use SAS tools for date handling (INTNX, INTCK, ...)

Do this instead:

intnx('month',datepart(t1.PRP_END_DATE),0,'b') format=yymmd7. as POLICY_VINTAGE,

The INTNX aligns the date to the first of the month, which is standard when using dates for periods.