topic Re: Import character date field in YYYY-Q1 format in SAS Programming

Import character date field in YYYY-Q1 format

Jim_Cooper_hmsa — Fri, 19 Mar 2021 00:02:31 GMT

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;

Re: Import character date field in YYYY-Q1 format

Reeza — Fri, 19 Mar 2021 00:31:13 GMT

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;

Re: Import character date field in YYYY-Q1 format

ChrisNZ — Fri, 19 Mar 2021 00:42:04 GMT

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;

Re: Import character date field in YYYY-Q1 format

Jim_Cooper_hmsa — Fri, 19 Mar 2021 00:46:08 GMT

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

Re: Import character date field in YYYY-Q1 format

ChrisNZ — Fri, 19 Mar 2021 00:50:31 GMT

Ngā mihi maioha