SAS Formats with Year and Quarter

[krueg314]

I am having a lot of trouble for something that should be easy.

I've used proc import to covert a .txt file to a .sas7bdat file and I am interested in one variable named date.

So the import coverts it to a BEST12. format and if I try to convert this year and quarter data which looks like

Have (BEST 12.)      Want (YYQC6.)

200001                     2000:1

200002                     2000:2

200003                     2000:3

200004                     2000:4

200101                     2001:1

...

The problem is that if I convert it to YYQC6. I get those dates based on the time from 1960, not the actual year and quarter I want. I could use substr and cats, but that doesn't give me the right format even though it looks like what I want. Any help on how to proceed?

Here is some starting code:

data have;
  input date:BEST12.;
  format date BEST12.;
  datalines;
200001
200002
200003
200004
200101
;
run;

proc contents data=have;
run;

[PaigeMiller]

When you read 200001 with informat BEST12., this is not a SAS date value representing the first quarter of 2000, and so formatting it with yyqc6. will fail. A valid SAS date value is the number of days since January 1, 1960. So you have to translate the text string 200001 to the text string 2000Q1, and then you can use the informat YYQ. to make this a valid SAS date value. Once it is a valid SAS date value, then formatting it with yyqc6. will work.

So, this works fine:

data have;
  input date:$6.;
  substr(date,5,1)='Q';
  date_wanted=input(date,yyq6.);
  format date_wanted yyqc6.;
  datalines;
200001
200002
200003
200004
200101
;
run;

but in this simple case, there's a simpler solution that does not involve formatting at all

data have;
  input date:$6.;
  substr(date,5,1)=':';
  datalines;
200001
200002
200003
200004
200101
;
run;

but this has the drawback that the DATE values are text strings, not numbers, and so any calendar function or additional formatting will fail (without additional effort). If your only goal is to display 200001 as 2000:1 then this is fine. If at some point you want to perform arithmetic or boolean logic with this value, you probably will want the first solution.

[Tom · Accepted solution]

So you read the string 200001 is if it was the number 200,001 but to use the YYQC format you need to have a date value instead. That is one of the problems with using PROC IMPORT,  it is forced to guess how to read the data so it assumed the string of digits represented a number.  It sounds like you want to treat that string as meaning the first quarter of 2000.  And 200002 as the second quarter.

You could convert your number into a date vlaue, then you could use that format to display it.

So here a simplified data step to create your example data (note there is no need for format or informat for simple numbers).

data have;
  input date ;
datalines;
200001
200002
200003
200004
200101
;

And here is a data step to read the existing dataset an convert the values of DATE into actual date value and attach the YYQC6. format specification to it.  You can use INT() and MOD() to split the number in the year and qtr value. You can use MDY() to make a date value and INTNX() to move it to the right quarter.

data want;
  set have;
  date = intnx('qtr',mdy(1,1,int(date/100)),mod(date,100)-1);
  format date yyqc6.;
run;

Result:

Obs      date

 1     2000:1
 2     2000:2
 3     2000:3
 4     2000:4
 5     2001:1

[Ksharp]

data have;
  input date:BEST12.;
  format date BEST12.;
  datalines;
200001
200002
200003
200004
200101
;
run;

data want;
  set have;
  new_date =yyq(int(date/100),mod(date,100));
  format new_date yyqc6.;
run;