How to assign a number to quarter(month)

[SeanZ]

Hi, I have a question, which I think it's not difficult. But I just don't know how to do.

I have a dataset including a date variable. The date variable is for quarter, and is not continuous. Look at the example below

date               quarter

1980.1               1

1980.2               2

1981.1               5

1981.3               7

...

1980.1 means the first quarter of 1980, and 1981.3 means the third quarter of 1981. I want to create the column "quarter". I treat 1980.1 as the first observed quarter, and others follow. If the date is continuous I know how to do. But for this case. I have no clue. Please help.

[RichardinOz]

You can use the INTCK () function (SAS speak for Intervals - count number of) to calculate the number of quarters from the base date.  However, to use the function you need to convert your 'date' into a SAS date within the quarter.  Something like this should do:

Data want;

     Set have ;

     By     id ;                    /* assuming you have an id to group your data and the data is sorted by the id */

     Retain Basedate ;

     Format SASdate Startdate ddmmyy10. ; /* mmddyy10 if you prefer */

     Year = INT(date) ;

     If first.id then Startdate =  mdy (1, 1, Year) - 1 ;     /*     Prev 31 Dec     */

     Month = 30 * (date - Year) ; /* values 3, 6, 9 etc */

     SASdate = mdy(Month, 1, Year) ;

     Quarter = INTCK ('QTR', Startdate, SASdate) ;

     Drop Year Month  ;

Run ;

If your date variable is character then

     Year = Input (date, 4.) ;

     Month = Input (Scan (date, -1, '.'), 1.) * 3 ;

If you have no id variable and all the quarters are calculated from the first record then delete the By ... statement and replace the If ... statement with

     If _N_ = 1 then Startdate =  mdy (1, 1, Year) - 1 ;     /*     Prev 31 Dec     */

Richard

Message was edited by: Richard Carson - mismatched quotes in the Quarter = expression.

[RichardinOz]

Two for the price of one - here is a solution using a lookup table (as a SAS informat) assuming your data is not grouped (no id):

Data Lookup ;

     Set have (obs = 1) ;

     Retain Fmtname 'Lookup'

               Type = 'I'

               ;

     YearStart = Int (Date) ;

     YearEnd = Year (Today) ;

     Do year = YearStart to YearEnd ;

          PrevYrs = Year - YearStart ;

          Do Q = 1 to 4 ;

              Start = Sum (Year, Q/10) ;

              Label = Put (Sum (PrevYrs * 4, Q), 4.) ;

          End ;

     End ;

Run ;

Proc Format Library=Work Cntlin = Lookup ;

Data Want ;

     Set Have ;

     Quarter = Input (Date, Lookup.) ;

Run ;

A Proc SQL solution using a lookup table is also possible.

Richard

[RichardinOz]

Before someone jumps in with a simpler solution, here 'tis:

Data want;

     Set have ;

     Retain YearStart ;

     Year = INT(date) ;

     If _N_ = 1 then

     YearStart = Year ;

     NumYears = Year - YearStart ;

     ThisQtr = (Date - Year) * 10 ;

     Quarter = NumYears * 4 + ThisQtr ;

     Drop     YearStart Year NumYears ThisQtr ;

Run ;

Apologies for overcooking the previous answers.

Ricahrd

[Peter_C]

This looks like a good opportunity to demo * macro function*

%macro yourQ( var, baseyear= 1980 );

4*((mod( &var, 1)-1)/4+int(&var)-&baseyear) +1

%mend  yourQ ;

Then use this like a function in a datastep

yourqno = %yourQ( yourDateVar ) ;

peterC

Message was edited by: Peter Crawford       you wanted to start at 1 not 0

[slchen]

data have;

input date $;

cards;

1980.1    

1980.2             

1981.1             

1981.3

;

run;

data want;

  set have;

  _date=input(scan(date,1)||'Q'||scan(date,-1),yyq9.);

  retain startyear;

  if _n_=1 then startyear=_date;

  quarter=intck('quarter',startyear,_date)+1;

   drop startyear _date;

  run;

[Ksharp · Accepted solution]

data have;
input date : yymmdd10.;
format date yyqp6.;
cards;
1980-02-01
1980-04-04
1981-01-01
1981-09-09
;
run;
data want(drop=base);
 set have;
 retain base .;
 if _n_ eq 1 then base=date;
 quarter=1+4*year(date)+qtr(date) - (4*year(base)+qtr(base));
run;

Xia Keshan