change format

[sasphd]

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;

[acordes]

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;

 

[sasphd]

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]

---

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

[sasphd]

data _mret_morningstar8;
set _mret_morningstar;

format date $40.;

date2=input(date, YYMMDD8.);

run;

this is a screen of sas 

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 · Accepted solution]

200006

Is not a valid DATE.

Do you want to treat that as 01JUN2000 ?

If so use the YYMMN6. informat.

[Tom]

---

@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]

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

[sasphd]

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]

---

@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]

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