BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasphd
Lapis Lazuli | Level 10

Hello, 

I want to change the date format. I write this 

data _mret_morningstar;
set _mret_morningstar;
format date YYMMDDN8.;
run;

 but sas give me an error 

 

635 data _mret_morningstar;
636 set _mret_morningstar;
637 format date YYMMDDN8.;
---------
48
ERROR 48-59: The format $YYMMDDN was not found or could not be loaded.

638 run;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User
200006

Is not a valid DATE.

 

Do you want to treat that as 01JUN2000 ?

If so use the YYMMN6. informat.

View solution in original post

10 REPLIES 10
acordes
Rhodochrosite | Level 12

what you do works for date variables stored as numeric.

your date is character, so you need to tell sas to interpret it as date.

 

 


data _null_;
ch_date='19981001';
ch_date2='01oct1998';

format date ddmmyy10. date2 yyq6.;

date=input(ch_date, YYMMDD8.);
date2=input(ch_date2, date9.);

put date= date2=;
run;

ok.png 

sasphd
Lapis Lazuli | Level 10

I put this 

 

data _mret_morningstar8;
set _mret_morningstar;

format date $40.;

date2=input(date, YYMMDD8.);

 

run;

but it does not work my date format is $40. and I wand YYMMDD8.

PaigeMiller
Diamond | Level 26

@sasphd wrote:

I put this 

 

data _mret_morningstar8;
set _mret_morningstar;

format date $40.;

date2=input(date, YYMMDD8.);

 

run;

but it does not work my date format is $40. and I wand YYMMDD8.


What about this doesn't work? Please explain. If there is an error in the log, show us the ENTIRE log for this data step. If you get the wrong answer, show us the wrong answer and what you think the right answer should be.

 

Also, show us a few example of the values in this variable named DATE.

--
Paige Miller
sasphd
Lapis Lazuli | Level 10

data _mret_morningstar8;
set _mret_morningstar;

format date $40.;

date2=input(date, YYMMDD8.);

 

run;

 

this is a screen of sas 

 

 

sasphd_0-1639419657916.png

 

this is error in the log


1207
1208 data _mret_morningstar8;
1209 set _mret_morningstar;
1210
1211 format date $40.;
1212
1213 date2=input(date, YYMMDD8.);
1214
1215
1216
1217 run;

NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198606 mret=0.6 date2=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198607 mret=0.6 date2=. _ERROR_=1 _N_=2
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198608 mret=2.27 date2=. _ERROR_=1 _N_=3
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198609 mret=1.23 date2=. _ERROR_=1 _N_=4
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198610 mret=3.16 date2=. _ERROR_=1 _N_=5
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198611 mret=0.46 date2=. _ERROR_=1 _N_=6
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198612 mret=2.96 date2=. _ERROR_=1 _N_=7
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198701 mret=2.52 date2=. _ERROR_=1 _N_=8
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198702 mret=2.63 date2=. _ERROR_=1 _N_=9
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198703 mret=5.81 date2=. _ERROR_=1 _N_=10
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198704 mret=6.3 date2=. _ERROR_=1 _N_=11
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198705 mret=5.77 date2=. _ERROR_=1 _N_=12
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198706 mret=11.45 date2=. _ERROR_=1 _N_=13
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198707 mret=7 date2=. _ERROR_=1 _N_=14
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198708 mret=0.73 date2=. _ERROR_=1 _N_=15
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198709 mret=0.85 date2=. _ERROR_=1 _N_=16
NOTE: Invalid argument to function INPUT at line 1213 column 7.
secid=F000000EU6 DATE=198710 mret=-17.46 date2=. _ERROR_=1 _N_=17

Tom
Super User Tom
Super User
200006

Is not a valid DATE.

 

Do you want to treat that as 01JUN2000 ?

If so use the YYMMN6. informat.

Tom
Super User Tom
Super User

@sasphd wrote:

I put this 

 

data _mret_morningstar8;
set _mret_morningstar;

format date $40.;

date2=input(date, YYMMDD8.);

 

run;

but it does not work my date format is $40. and I wand YYMMDD8.


 

SAS has just two types of variables, fixed length character string and floating point numbers.

 

A format attached to a variable just controls the values are printed.   There is no need to attach the $40. to a character variable, SAS does not need special instructions for how to display character variables.

 

SAS uses INFOMATs to control how to convert text into values. 

 

Frequently there will be formats and informats that have the same or similar name, but they are two different things that serve two different needs.

 

If you have character variable it does not really matter what format you have attached to it. What does matter is what strings are in the variable. That will determine what informat is appropriate to use to convert the string into a date value.  So if your strings look like "20211213" or "2021-12-13" or "2021/12/13" then you could use YYMMDD10. informat to convert them into date values.  If the strings look like "13DEC2021" instead then you need to use the DATE11. infromat instead. If your character variable is really 40 bytes long then to be sage remove any leading spaces that might be in the variable.

 

SAS will store dates as numbers.  It will store DATE values as the number of days since start of 1960. It will store TIME as the number of seconds since midnight.  It will store DATETIME as the number of seconds since 1960.  You can then attach a format to print the date/time/datetime values in many different styles.  If you want the dates to print like "2021-12-13" then use the YYMMDD10. format.  If you want the dates to print like "20211213" then use the YYMMDDN8. format, but that seems like it would just confuse people because it looks the same as the number 2,0211,223.

 

data _mret_morningstar8;
  set _mret_morningstar;
  date2=input(left(date), YYMMDD10.);
  format date2 yymmdd10.;
run;
sasphd
Lapis Lazuli | Level 10

 

 

sasphd_0-1639420096462.png

I import data from excel so the data is year and month only. for example 201001 is 2010 january 

sasphd
Lapis Lazuli | Level 10

I think that my problem of date format come when I import data from excel 

date become like text 

can you help me to import correcty date form excel 

proc import
datafile = 'C:\MaRecherche\Value added infrastructure MFs\database\_0_Fundname_Morningstar.xlsx'
DBMS = XLSX
OUT = mret_morningstar ;
sheet="mret_morningstar";
run;

have

secid  01-01-1984 01-02-1984 

1             0.2               0.6

1              0.4               0.96

ballardw
Super User

@sasphd wrote:

I think that my problem of date format come when I import data from excel 

date become like text 

can you help me to import correcty date form excel 

proc import
datafile = 'C:\MaRecherche\Value added infrastructure MFs\database\_0_Fundname_Morningstar.xlsx'
DBMS = XLSX
OUT = mret_morningstar ;
sheet="mret_morningstar";
run;

have

secid  01-01-1984 01-02-1984 

1             0.2               0.6

1              0.4               0.96


Very likely. About the single most common question category on these forums has been related to Proc Import, or import wizards which use that procedure in the background, and Excel data.

One of the serious problems with Excel as a data interchange format is that Excel does not impose any rules on what goes in a cell or where data starts. Proc Import expects at most one row of column headers in the source file. If you have more than one row of column headers then it is very likely the SAS rules in Proc Import will create any variables under those headers as character.

Second, Dates are numeric and are not proper SAS variable names. Read the rules in the documentation. So your picture is not going to have a "date" that can be formatted as such because column headings become variable names, not values. So you would need to do some further processing of anything that looks like that to create "date" values. What you show above is not all like the previous post, that looks like possibly transposed data, and the "label of former variable" could be used to create a date with that value. That would require use of the YYMMN6. informat and would result in a date value corresponding to the first day of the month.

 

So, provide a working example of what you actually have.

Tom
Super User Tom
Super User

Does this represent the original XLSX file? Or the result you get in your SAS dataset?

secid  01-01-1984 01-02-1984 
1             0.2               0.6
1              0.4               0.96

What are those strings with the hyphens in them?  They cannot be used as variable names without setting the VALIDVARNAME option to ANY.

What date do those strings present? Are those supposed to be the first two days of 1984? Or the first two months?  (If you avoid displaying dates in either MDY or DMY order you can avoid confusing 50% of your audience.)

 

Let's assume you have a dataset like this after your PROC IMPORT step.

options validvarname=any;
data have;
  input secid  '01-01-1984'n '01-02-1984'n;
cards; 
1  0.2  0.6
1  0.4  0.96
;

So you could convert that into something useful by "transposing" it. That is moving the DATE out of the variable name and into an actual variable.

data want;
  set have ;
  array xx '01'n: ;
  do index=1 to dim(xx);
     date = input(vname(xx[index]),ddmmyy10.);
     value = xx[index];
     output;
  end;
  format date yymmdd10.;
  drop '01'n: ;
run;

Results:

Obs    secid    index          date    value

 1       1        1      1984-01-01     0.20
 2       1        2      1984-02-01     0.60
 3       1        1      1984-01-01     0.40
 4       1        2      1984-02-01     0.96

 

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1792 views
  • 6 likes
  • 5 in conversation