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.
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
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.
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
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
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.