BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

convert sas numeric date to sas char date:

What is the reason that the required fields getting null value in this program?

I want to get char dates in format YYYY-MM-DD

data have;
input x1 x2 x3 x4 x5;
cards;
20010101 20010101 20010101 20010101 20010101
20010801 20010701 20010301 20010122 20010115
;
Run;


data want;
set have;
/*array*/
Array t(5) X1 X2 X3 X4 X5;
Array q(5) _X1 _X2 _X3 _X4 _X5;
Array r(5) want_X1 want_X2 want_X3 want_X4 want_X5;
do i=1 to 5;
IF t(i) =0 then q(i)=.;
else q(i)=input(compress(put(t(i),best.)),yymmdd8.);/****SAS date***/
r(i)=put(q(i),YYMMDD10.);/**Convert sas date to char***/
end;
drop i;
Run;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@Ronein wrote:

It means char value with structure  YYYY-MM-DD

I need it as input for other system.

This is the reason that I neec char values with structure  of date YYYY-MM-DD


Is the other system using the SAS data set directly or do you export the data from SAS to a text, or other layout, file that is read?

 

When you ask:" What is the reason that the required fields getting null value in this program?" did you read your log?

When I run your code this is the result:

10   data want;
11   set have;
12   /*array*/
13   Array t(5) X1 X2 X3 X4 X5;
14   Array q(5) _X1 _X2 _X3 _X4 _X5;
15   Array r(5) want_X1 want_X2 want_X3 want_X4 want_X5;
16   do i=1 to 5;
17   IF t(i) =0 then q(i)=.;
18   else q(i)=input(compress(put(t(i),best.)),yymmdd8.);/****SAS date***/
19   r(i)=put(q(i),YYMMDD10.);/**Convert sas date to char***/
20   end;
21   drop i;
22   Run;

NOTE: Character values have been converted to numeric
      values at the places given by: (Line):(Column).
      19:1
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
x1=20010101 x2=20010101 x3=20010101 x4=20010101 x5=20010101 _X1=14976
_X2=14976 _X3=14976 _X4=14976 _X5=14976 want_X1=. want_X2=. want_X3=.
want_X4=. want_X5=. i=6 _ERROR_=1 _N_=1
NOTE: Invalid numeric data, '2001-08-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-07-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-03-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-22' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-15' , at line 19 column 6.
x1=20010801 x2=20010701 x3=20010301 x4=20010122 x5=20010115 _X1=15188
_X2=15157 _X3=15035 _X4=14997 _X5=14990 want_X1=. want_X2=. want_X3=.
want_X4=. want_X5=. i=6 _ERROR_=1 _N_=2
NOTE: There were 2 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 2 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time

Problems: First you say you want a character value. Then you create an array of numeric values. If your "want" array is to hold character values then you define it as character.

Second, you need to look at the results of this:

put(t(i),best.)

The Best format without width defaults to 12. So your result has 4 leading spaces. Which then FAILS miserably with the input function.

 

Does your other program actually expect a . when your original "date" is 0? Sounds a bit flaky to me.

 

Suggestion: if you are going to have code that tests for bad values, such as a "date" of 0, then provide at least one value in the data. If your use of "if t[i]=0 was to avoid the invalid data messages then there are better options. See below:

 

data have;
input x1 x2 x3 x4 x5;
cards;
20010101 20010101 20010101 20010101 20010101
20010801 20010701 20010301 20010122 0
;
Run;


data want;
   set have;
   Array t(5) X1 X2 X3 X4 X5;
   Array r(5) $10 want_X1 want_X2 want_X3 want_X4 want_X5 ;
   do i=1 to 5;
       r[i]= put(input(put(t[i],8. -L),?? yymmdd8.),yymmddd10.);
   end;
   drop i;
Run;

 

The ?? option on the input will suppress invalid data messages. NOTE use of the 8. format instead of best. Also the -L option on Put left justifies the result so you don't get leading spaces.

 

And last, never call a randomish string of digits like 20200122 a "date". It is a number perhaps but not a date except to people. If you add 1 to 20201231 you do not get 20210101 (jan 1 of the next year) you get a value of 20201232 which is just wrong for anything considered a "date".

 

 

 

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

No such thing as "SAS char date".

 

As mentioned a thousand times already, dates should remain numeric, and you can format them to appear in many different ways, whatever you want.

 

So, don't do this.

--
Paige Miller
Ronein
Meteorite | Level 14

It means char value with structure  YYYY-MM-DD

I need it as input for other system.

This is the reason that I neec char values with structure  of date YYYY-MM-DD

ballardw
Super User

@Ronein wrote:

It means char value with structure  YYYY-MM-DD

I need it as input for other system.

This is the reason that I neec char values with structure  of date YYYY-MM-DD


Is the other system using the SAS data set directly or do you export the data from SAS to a text, or other layout, file that is read?

 

When you ask:" What is the reason that the required fields getting null value in this program?" did you read your log?

When I run your code this is the result:

10   data want;
11   set have;
12   /*array*/
13   Array t(5) X1 X2 X3 X4 X5;
14   Array q(5) _X1 _X2 _X3 _X4 _X5;
15   Array r(5) want_X1 want_X2 want_X3 want_X4 want_X5;
16   do i=1 to 5;
17   IF t(i) =0 then q(i)=.;
18   else q(i)=input(compress(put(t(i),best.)),yymmdd8.);/****SAS date***/
19   r(i)=put(q(i),YYMMDD10.);/**Convert sas date to char***/
20   end;
21   drop i;
22   Run;

NOTE: Character values have been converted to numeric
      values at the places given by: (Line):(Column).
      19:1
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-01' , at line 19 column 6.
x1=20010101 x2=20010101 x3=20010101 x4=20010101 x5=20010101 _X1=14976
_X2=14976 _X3=14976 _X4=14976 _X5=14976 want_X1=. want_X2=. want_X3=.
want_X4=. want_X5=. i=6 _ERROR_=1 _N_=1
NOTE: Invalid numeric data, '2001-08-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-07-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-03-01' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-22' , at line 19 column 6.
NOTE: Invalid numeric data, '2001-01-15' , at line 19 column 6.
x1=20010801 x2=20010701 x3=20010301 x4=20010122 x5=20010115 _X1=15188
_X2=15157 _X3=15035 _X4=14997 _X5=14990 want_X1=. want_X2=. want_X3=.
want_X4=. want_X5=. i=6 _ERROR_=1 _N_=2
NOTE: There were 2 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 2 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time

Problems: First you say you want a character value. Then you create an array of numeric values. If your "want" array is to hold character values then you define it as character.

Second, you need to look at the results of this:

put(t(i),best.)

The Best format without width defaults to 12. So your result has 4 leading spaces. Which then FAILS miserably with the input function.

 

Does your other program actually expect a . when your original "date" is 0? Sounds a bit flaky to me.

 

Suggestion: if you are going to have code that tests for bad values, such as a "date" of 0, then provide at least one value in the data. If your use of "if t[i]=0 was to avoid the invalid data messages then there are better options. See below:

 

data have;
input x1 x2 x3 x4 x5;
cards;
20010101 20010101 20010101 20010101 20010101
20010801 20010701 20010301 20010122 0
;
Run;


data want;
   set have;
   Array t(5) X1 X2 X3 X4 X5;
   Array r(5) $10 want_X1 want_X2 want_X3 want_X4 want_X5 ;
   do i=1 to 5;
       r[i]= put(input(put(t[i],8. -L),?? yymmdd8.),yymmddd10.);
   end;
   drop i;
Run;

 

The ?? option on the input will suppress invalid data messages. NOTE use of the 8. format instead of best. Also the -L option on Put left justifies the result so you don't get leading spaces.

 

And last, never call a randomish string of digits like 20200122 a "date". It is a number perhaps but not a date except to people. If you add 1 to 20201231 you do not get 20210101 (jan 1 of the next year) you get a value of 20201232 which is just wrong for anything considered a "date".

 

 

 

Ronein
Meteorite | Level 14
Thanks a lot!
What is the meaning of ?? that you wrote
ballardw
Super User

@Ronein wrote:
Thanks a lot!
What is the meaning of ?? that you wrote

As my explanation says, it suppresses invalid data messages, just like the similar option on the Input statement.

Kurt_Bremser
Super User
Why do you have such useless numbers in the first place?
Dates MUST ALWAYS be stored correctly, as counts of days, with a proper SAS format to be human-readable.
So go back to the process that brings the data into SAS, fix it, and then it‘s a simple PUT with YYMMDD10.
Ronein
Meteorite | Level 14

You are right but sometimes we get data sets with meaning of dates but with non sas dates values.

I agree 100% that always need to write SAS dates in SAS dates fields but unfortunately we are living in a word when data is not always built well and then need to convert it.

 

Tom
Super User Tom
Super User

Just fix the ARRAY statement so that it defines a set of CHARACTER variables.

data have;
  input x1-x5;
cards;
20010101 20010101 20010101 20010101 20010101
20010801 20010701 20010301 20010122 20010115
;
  
data want;
  set have;
  array t(5) X1-x5;
  array q(5) _X1-_X5;
  array r(5) $10 want_X1-want_X5;
  do i=1 to 5;
    if t(i) ne 0 then q(i)=input(put(t(i),z8.),yymmdd8.);
    r(i)=put(q(i),YYMMDD10.);
  end;
  drop i;
  format _x1-_x5 yymmdd10.;
run;

Tom_0-1687702997165.png

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 8 replies
  • 480 views
  • 4 likes
  • 5 in conversation