topic Re: How to assign a number to quarter(month) in SAS Programming

How to assign a number to quarter(month)

SeanZ — Tue, 06 May 2014 06:16:15 GMT

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.

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

RichardinOz — Tue, 06 May 2014 06:57:24 GMT

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.

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

RichardinOz — Tue, 06 May 2014 07:16:32 GMT

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 ;

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

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

Peter_C — Tue, 06 May 2014 07:53:30 GMT

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

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

RichardinOz — Tue, 06 May 2014 07:56:43 GMT

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

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

slchen — Tue, 06 May 2014 10:48:32 GMT

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;

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

Ksharp — Tue, 06 May 2014 10:49:10 GMT

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