Variable is being read as a character when it should be numeric. Proc import and datastep dont work

[clarinaje]

I have a dataset with a variable that should be numeric, but SAS is reading it as a character variable, and I am having a hard time converting it to numeric. The variable is a YearMonth variable (ie: 2023M01) so I'm wondering if the M in there is causing a problem. I have tried importing the dataset in a proc import and then doing a data step right after to convert from char to num but that doesn't work - it returns "."  I have also tried to import the dataset in a proc import and specifying the length of the var, and the informat and format ((YYMM.). That also doesn't work. I really need this variable to be numeric because the remaining code uses this variable for calculations. And I'm frustrated because I can't seem to figure this out. I am attaching some images to go along with I wrote for more clarity. 

[PaigeMiller]

Getting rid of the letter M is easy with the COMPRESS function

data want;
    set have;
    numericvar=input(compress(summaryYM,'m','i'),yymmn6.);
    format numericvar date9.;
run;

From now on, please post code as text and not screen captures. Use the SAS code box (click on the little running man icon) to open it and paste your code into the SAS code box.

[JOL]

You can use the ANYDTDTE informat to read your date, however to must use a special character to separate the year from the month not an "M". This can be done with the TRANWRD function. 

data test;
cdate = "2023M01";
cdate = tranwrd(cdate,"M","-");
ndate = input(cdate,anydtdte.);
format ndate yymm.;
run;

[Tom]

I am curious why you say "SAS is reading it as a character variable".  Didn't you TELL SAS what to do?

Or did you ask SAS to make its own GUESS as to how to read the variable?  Perhaps by using PROC IMPORT?

If the file is a delimited text file (aka a CSV file) then just write the data step to read it yourself and SAS will read it the way you tell it to read it.

But that said I do not think that SAS has in informat that can read strings in that style as a date value.  So you will need to either read it into a character variable and then deal with it from there.  Or perhaps read it into two numeric variables (which would probably require that you be reading form a fixed position text file instead of a delimited file).

So you might pull out the month number and year number and use them to build a date:

data want;
  set have;
  date = mdy(input(substr(summaryYM,6),2.),1,input(summaryYM,4.));
  format date yymm7.;
run;

Or convert the string to something that can be read by an existing informat:

data want;
  set have;
  date = input(compress(summaryYM,'Mm'),yymmn6.);
  format date yymm7.;
run;

[ballardw]

The likelihood of a number read with a 6. informat being usable with any date format correctly approaches 0. In fact I believe the only YYYYMM appearing numeric value that matches the a formatted date value using the YYMMN6. format is 269809. That is 17Sep2689.

Plus you say your values are like "2023M01" and then try to read them with a 6. informat?? Read 7 character value with a 6. informat and it reads "2023M0", so would never have a month value to work with.

[Tom]

Since SAS does not provide an informat YYMM that can read the strings generated by the YYMM format you might consider just making your own:

proc format;
  invalue yymm 's/[Mm]//' (regexpe) = [yymmn6.] ;
run;

Then you could use that:

data have;
  input summaryYM $7. ;
cards;
2023M01
2023M02
2023M03
2023M04
2023M05
;

data want;
  set have;
  date = input(summaryYM, yymm.);
  format date yymm7.;
run;

proc print;
run;

Results: