Convert date 201001 to JAN2010

[Santt0sh]

Hi All,
I am trying to convert a char date value to MONyy7.

I am not able to get the desired output.


Data _NULL_;
Month = intnx(‘month’, Today(), -13);
Put monyy7.;
Run;

Output ::::: MAR2021

I am reading file names from a dataset.

For example:

Data Month;
Mnth = xxx_xxxxx_2010;
Dte = scan(‘month’,-1,’_’);
Dte1=(input(scan(‘Month’,Best12.);
Run;

I’m not able to apply any of the date informats tried Anydttdtm and anydttdte


Kindly suggest

[Santt0sh]

Hi All,

I missed to complete the scan in the previous post please find the completed and working
Data Month;
Mnth = xxx_xxxxx_2010;
Dte = scan(‘month’,-1,’_’);
Dte1=(input(scan(‘Month’,-1,’_’),Best12.);
Run;

[Kurt_Bremser]

This cannot work:

Data Month;
Mnth = xxx_xxxxx_2010;
Dte = scan(‘month’,-1,’_’);
Dte1=(input(scan(‘Month’,-1,’_’),Best12.);
Run;

Since you do not read any data from a dataset or an external file, there will not be a variable named xxx_xxxxx_2010, so you get a missing value in the first assignment.

Then you use "funny" curly quotes which are not valid in code.

If you want to separate the "2010" out from a string containing "xxx_xxxx_2010", you need to use (valid) quotes around the string in the first place; next, you have to use the variable in the next assignment, not a string containing "month". Also, avoid using unnecessary parentheses (start of the third assignment).

data month;
mnth = "xxx_xxxxx_2010";
dte = scan(mnth,-1,'_');
dte1 = input(scan(mnth,-1,'_'),best12.);
run;

For your initial question, this converts the string to a SAS date with the wanted format:

data _null_;
indate = "201001";
outdate = input(indate,yymmn6.);
format outdate monyy7.;
put outdate=;
run;

[Santt0sh]

Hi Kurt,

Thank you for your quick response!

Yes, you were correct, I missed putting xxx_xxxx_201001 in quotations.



Data _NULL_;
Month = intnx('month', Today(), -13);
Put Month monyy7.;
Run;


data xxx;
fle = 'xxx_xxxx_2020'; /*filename*/
a = scan(fle,-1,'_');
a1=(input(scan(fle,-1,'_'),best12.));
a2=(input(scan(fle,-1,'_'),6.));
a3=(put(scan(fle,-1,'_'),monyy7.));
format a1 monyy7. a2 monyy7. ;
run;

[Kurt_Bremser]

Maxim 2: Read the Log.

 69         data xxx;
 70         fle = 'xxx_xxxx_2020'; /*filename*/
 71         a = scan(fle,-1,'_');
 72         a1=(input(scan(fle,-1,'_'),best12.));
 73         a2=(input(scan(fle,-1,'_'),6.));
 74         a3=(put(scan(fle,-1,'_'),monyy7.));
                                     _______
                                     484
 NOTE 484-185: Format $MONYY was not found or could not be loaded.

The fact that the data step compiler looked for a character format tells you that you wanted to apply a numeric format to a character value. The compiler then tries to correct your mistake and goes looking for a fitting character format, which is not there (in this case).

Before you continue I VERY STRONGLY recommend that you study this first to get an understanding how dates and times are represented in SAS.

Dates are counts of days, times and datetimes are counts of seconds. Dates and datetimes start at midnight 1960-01-01, while times start at midnight of a given day.

A value of 2020 will therefore be interpreted by a date format as the 2020th day after 1960-01-01, which happens to be 1965-07-13.

[Santt0sh]

Hi Kurt,

Thank you for your suggestion.

I missed it earlier.


It's working for me now.
Tahnk you again!

[PaigeMiller]

Data _NULL_;
Month = intnx('month', Today(), -13);
Put monyy7.;
Run;

This doesn't work, and the LOG should report an error. You have to tell the PUT statement what variable you want it to PUT.

I am reading file names from a dataset.

For example:

Data Month;
Mnth = xxx_xxxxx_2010;
Dte = scan(‘month’,-1,’_’);
Dte1=(input(scan(‘Month’,Best12.);
Run;

I’m not able to apply any of the date informats tried Anydttdtm and anydttdte

What is xxx_xxxxx_2010? I don't know what this is, so I am not surprised that informats cannot work with it. Day of the month is 3 characters and month is 5 characters? Or month is 3 characters and day of month is 5 characters? Please give us an actual example of this text string.

Furthermore, your code has mismatched parentheses, you also need to fix that before anything will work.

[Santt0sh]

Hi Paige,

Thank you for your quick response!
Please find the corrected code below, but I m still not getting what I want to. i.e. convert 202003 to march2020

Data _NULL_;
Month = intnx('month', Today(), -13);
Put Month monyy7.;
Run;


data xxx;
fle = 'xxx_xxxx_202003'; /*filename*/
a = scan(fle,-1,'_');
a1=(input(scan(fle,-1,'_'),best12.));
a2=(input(scan(fle,-1,'_'),6.));
a3=(put(scan(fle,-1,'_'),monyy7.));
a4=(input(scan(fle,-1,'_'),monyy7.));
format a1 monyy7. a2 monyy7. ;
run;

proc contents data = xxx;run;

[PaigeMiller]

202003 is four digit year and two digit month, so you need the YYMMN. informat.

data a;
    fle='xxx_xxxx_202003';
    month=input(scan(fle,-1,'_'),yymmn6.);
    format month monyy7.;
run;

[Tom · Accepted solution]

Let's clean up your code and use the Insert SAS Code pop-up window so it is easier to read.

data xxx;
  fle = 'xxx_xxxx_202003';
  a = scan(fle,-1,'_');
  a1= input(a,best12.);
  a2= input(a,6.);
  a3= put(a,monyy7.);
  a4= input(a,monyy7.);
  format a1 monyy7. a2 monyy7. ;
run;

So you created 5 variables in that code. FLE, A and A3 are character variables.  A1, A2 and A4 are numeric variables.

FLE appears to be your source string.

A is just just the part after the last underscore.  If there are no underscores then FLE and A are the same.

So for A1 and A2 you are attempting the same thing it different ways.  With A1 you attempted to use BEST as an informat, but BEST is the name of a FORMAT.  It you use it as an INFORMAT then SAS just treats it as an alias for the normal numeric informat.

So you really ran this code:

a1= input(a,12.);
a2= input(a,6.);

That will convert the first 12 and first 6 bytes, respectively,  of A into numbers. Since INPUT() doesn't care if the informat width is larger than the length of the string you can just use the maximum width on your informat instead.

a1= input(a,32.);

For your example value of FLE this means that A1 will have the number 202,003.  Which if you try to format that number as a date will be some time in the year 2513.  So A1 and A2 are NOT the values you want.

The statement creating A3 does not make any sense.

a3= put(a,monyy7.);

Since A is a character variable and you are using a numeric format SAS will convert the characters in A into a number.  That will be the same number that represents some day in the 2513 that you got in A1 and A2. So A3 is meaningless.

The statement for A4 is closer. At least now you are trying to create a date value into A4 by using an date type informat.  Unfortunately the string you pulled from FLE is not in the right style for the MONYY informat.  You need to use the YYMMN informat instead.  That will recognized digit strings that follow the pattern yyyymm or yymm as dates.

So in the end you want:

data xxx;
  fle = 'xxx_xxxx_202003';
  date = input(scan(fle,-1,'_'),yymmn6.);
  date_str = put(date,monyy7.);
  format date monyy7.;
run;

[Santt0sh]

Hi Tom,

Thank you for your quick response!

I will try this too..

Regards