Import character date field in YYYY-Q1 format

[Jim_Cooper_hmsa]

I have a MS SQL-Server table that contains a character field with date values representing a quarter year with the format YYYY-Q1.  The SAS provided informat YYQ. won't work because the field contains a hyphen (-) between the year and the quarter.

I have tried creating a picture informat, but with no success.

proc format;

picture yyqtr
low-high = "%Y-Q%q" (DATATYPE=datetime);

run;

[Reeza]

Use COMPRESS() to remove the -?

want = input(compress(var, "-"), YYQ6.);

---

@Jim_Cooper_hmsa wrote:

I have a MS SQL-Server table that contains a character field with date values representing a quarter year with the format YYYY-Q1.  The SAS provided informat YYQ. won't work because the field contains a hyphen (-) between the year and the quarter.

I have tried creating a picture informat, but with no success.

proc format;

picture yyqtr
low-high = "%Y-Q%q" (DATATYPE=datetime);

run;

---

[ChrisNZ · Accepted solution]

The picture syntax is only for formats.

If you want to create an informat, this works:

proc format;
  invalue nodash 's/-//' (regexpe)=[yyq6.];
data T;
  DATE=input('2020-Q1',nodash7.);
run;

[Jim_Cooper_hmsa]

Chris,

This is very cool.  I think that I was approaching the problem from the wrong direction.  i should have been looking for an INVALUE solution instead of a PICTURE solution,  This is awesome.

Mahalo Nui

[ChrisNZ]

Ngā mihi maioha