BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gieorgie
Quartz | Level 8

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

Gieorgie_0-1635332945511.png

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

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
Quartz | Level 8
Its not helped same error shows
AMSAS
SAS Super FREQ

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
SAS Super FREQ

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
Opal | Level 21

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
Quartz | Level 8
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
Opal | Level 21

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
Quartz | Level 8
Thanks ! i resolved this i changed data to string ,put(datepart(t1.PRP_END_DATE),yymmd7.) as POLICY_VINTAGE,
Kurt_Bremser
Super User

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 2864 views
  • 5 likes
  • 5 in conversation