BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SeanZ
Obsidian | Level 7

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
Ksharp
Super User
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

View solution in original post

6 REPLIES 6
RichardinOz
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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
Rhodochrosite | Level 12

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
Lapis Lazuli | Level 10

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
Super User
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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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