BookmarkSubscribeRSS Feed
KiranMaddi
Obsidian | Level 7

Hello all,

 

I have been having issues converting date ths is in yymmdd10. to ddmmyy10.

 

I have been trying this code

 

, input(LVRTEDAT,yymmdd10.) as LVRTEDAT format ddmmyy10.
, input(LVPURDAT,yymmdd10.) as LVPURDAT format ddmmyy10.

 

 

ERROR: INPUT function requires a character argument.
ERROR: Character expression requires a character format.
ERROR: INPUT function requires a character argument.
ERROR: Character expression requires a character format.

LVTREDAT is a variable pulling from sql which is in yymmdd10.

 

I have also tried the below code and coming up with the below error

 

format LVINPDAT ddmmyy10.;
format LVRTEDAT ddmmyy10.;
format LVPURDAT ddmmyy10.;

 

ERROR: CLI cursor fetch error: [Microsoft][SQL Server Native Client 11.0]Numeric value out of range

 

FULL ACTUAL CODE:

 

proc sql exec;
connect to odbc (&connectA);
create table pveh as
select
LVPOLNO
, LVVEHNO
, LVVEHPRM
, LVVEHPRC
, LV1SPR2A
, LVALWDRV
, LVPLNNO
, LVNCD
, LVRGDPRS
, LVBASEXS
, LVADDEXS
, input(LVINPDAT,yymmdd10.) as LVINPDAT format ddmmyy10.
, input(LVRTEDAT,yymmdd10.) as LVRTEDAT format ddmmyy10.
, input(LVPURDAT,yymmdd10.) as LVPURDAT format ddmmyy10.
, LVNCDPRO
, LVCVRTYP
, LVUSECDE
, LVINSVAL
, LVVEHMOD
, LVVEHBDY
, LVCHPFCT
, LVVEHYRL
, LV1SPR7P0
, LVANNMLG
, LVBRKCVR
, LVLGLPRO
, LV2SPR1A
, LVMOPCDE
from connection to odbc
(select * from OPENQUERY
(BETA,'
Select
*
from
budadm.pveh as A
where
a.lVpolno >= '&min'
and a.lVpolno <= '&max'
and a.lVamdno = 1
')A)B;
disconnect from odbc;
quit;
run;

 

Your help will be much appreciated. Thanks 🙂

9 REPLIES 9
mohamed_zaki
Barite | Level 11

Your code look fine.

Could you select some rows without formating from these columns and post them here?

KiranMaddi
Obsidian | Level 7

Here are the unformatted columns. They are in date9. format. changed the format in the input statement and that's not working either


Capture.JPG
mohamed_zaki
Barite | Level 11

I do not understand your dates are already saved in which format  date9. or yymmdd10 ???

KiranMaddi
Obsidian | Level 7
Just tried the below code and not sure why I am ending up with the below error?

proc sql exec;
connect to odbc (&connectA);
create table pveh as
select *

from connection to odbc
(select * from OPENQUERY
(BETA,'
Select
*
from
budadm.pveh as A
where
a.lVpolno >= '&min'
and a.lVpolno <= '&max'
and a.lVamdno = 1
')A)B;
disconnect from odbc;
quit;
run;


ERROR: CLI cursor fetch error: [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Macro variables e.g. &min need to be within double quotes to be dereferenced, e.g.:

a.lVpolno >= "&min"

 

What is it your doing, it looks like your passing a query through to a database?  Until your familiar with doing such a thing, just select * from table, get the data back to SAS and then process it.  If you want a response on your original question, do the above, get the data to SAS, and then post of proc contents of the retrieved data, and example of what it looks like.  Its likely you will get ISO dates, or date/times back from the database.

Ksharp
Super User
a.lVpolno >= '&min'
and a.lVpolno <= '&max'

-->

a.lVpolno >= %unquote(%nrbquote('&min'))
and a.lVpolno <=%unquote(%nrbquote('&max'))
bharathkumar
Fluorite | Level 6

Hi,

 

Try this way...

 

input(put(LVRTEDAT,10.),yymmdd10.)) as LVRTEDAT format ddmmyy10.
, input(put(LVPURDAT,10.),yymmdd10.)) as LVPURDAT format ddmmyy10.

 

Thanks,

Bharath

KiranMaddi
Obsidian | Level 7
Already tried that before. Din't work.
Astounding
PROC Star

Have you tried simplifying the code:

 

,  LVINPDAT format ddmmyy10.

 

The variable already has the proper value, you just need to change the format.

 

Since you are getting a message about values out of range, it is conceivable that you have a date-time variable and need to extract the date part:

 

, datepart(lvinpdat) as lvinpdat format ddmmyy10.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 4247 views
  • 0 likes
  • 6 in conversation