Obsidian | Level 7

How to assign a number to quarter(month)

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: How to assign a number to quarter(month)

```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

6 REPLIES 6
Quartz | Level 8

Re: How to assign a number to quarter(month)

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.

Quartz | Level 8

Re: How to assign a number to quarter(month)

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

Quartz | Level 8

Re: How to assign a number to quarter(month)

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

Rhodochrosite | Level 12

Re: How to assign a number to quarter(month)

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

Lapis Lazuli | Level 10

Re: How to assign a number to quarter(month)

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;

Super User

Re: How to assign a number to quarter(month)

```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

Discussion stats
• 6 replies
• 4699 views
• 6 likes
• 5 in conversation