Dynamically create Fiscal Year and Quarters variable for repeated ids

[Paakay]

Hi, 

I want to dynamically create Fiscal Year variable and Quarter for each repeated id in a dataset. There are 144,272 records. Each id is repeated 16 times in the dataset. So first four records for id 1 will be assigned Fiscal Year 2018 and assigned Quarter for 1 for first record, Quarter 2 for second record, Quarter three for third record snd Quarter 4 for the fourth record. Records 5 to 8 for id 1 will be assigned Fiscal Year 2019 and 5th record will be assigned Quarter1, 6th record Quarter 2, 7th record Quarter3 and 8th record Quarter4. I want to generate the Fiscal and Quarter variable dynamically for all records. Can someone help me out. The sas code below produces the output I want.

Data have;
input id Fiscal_Year Quarter;
datalines;
1 2018 1
1 2018 2
1 2018 3
1 2018 4
1 2019 1
1 2019 2
1 2019 3
1 2019 4
1 2020 1
1 2020 2
1 2020 3
1 2020 4
1 2021 1
1 2021 2
1 2021 3
1 2021 4
;
run

[PaigeMiller]

---

@Paakay wrote:

Hi, 

I want to dynamically create Fiscal Year variable and Quarter for each repeated id in a dataset. There are 144,272 records. Each id is repeated 16 times in the dataset. So first four records for id 1 will be assigned Fiscal Year 2018 and assigned Quarter for 1 for first record, Quarter 2 for second record, Quarter three for third record snd Quarter 4 for the fourth record. Records 5 to 8 for id 1 will be assigned Fiscal Year 2019 and 5th record will be assigned Quarter1, 6th record Quarter 2, 7th record Quarter3 and 8th record Quarter4. I want to generate the Fiscal and Quarter variable dynamically for all records. Can someone help me out. The sas code below produces the output I want.

Data have;
input id Fiscal_Year Quarter;
datalines;
1 2018 1
1 2018 2
1 2018 3
1 2018 4
1 2019 1
1 2019 2
1 2019 3
1 2019 4
1 2020 1
1 2020 2
1 2020 3
1 2020 4
1 2021 1
1 2021 2
1 2021 3
1 2021 4
;
run

---

Use a Cartesian join

proc sql;
     create table want as select a.*,b.fiscal_year,b.quarter
     from some_data_set as a,have as b;
quit;

[Reeza]

1. At the start of each ID set the date to January 1, 2018

2. Use RETAIN to keep the date variable across rows

3. If not the first date, increment the date by one quarter using INTNX

4. Use the YEAR/QTR functions to extract the year/quarter components. Depending on what you're doing, you could consider just keeping the date and using formats. 

Tested and works:

data want;
set have;
by id;
retain date;

if first.id then date = '01Jan2018'd;
else date = intnx('qtr', date, 1, 's');

year = year(date);
quarter = qtr(date);

run;

---

@Paakay wrote:

Hi, 

I want to dynamically create Fiscal Year variable and Quarter for each repeated id in a dataset. There are 144,272 records. Each id is repeated 16 times in the dataset. So first four records for id 1 will be assigned Fiscal Year 2018 and assigned Quarter for 1 for first record, Quarter 2 for second record, Quarter three for third record snd Quarter 4 for the fourth record. Records 5 to 8 for id 1 will be assigned Fiscal Year 2019 and 5th record will be assigned Quarter1, 6th record Quarter 2, 7th record Quarter3 and 8th record Quarter4. I want to generate the Fiscal and Quarter variable dynamically for all records. Can someone help me out. The sas code below produces the output I want.

Data have;
input id Fiscal_Year Quarter;
datalines;
1 2018 1
1 2018 2
1 2018 3
1 2018 4
1 2019 1
1 2019 2
1 2019 3
1 2019 4
1 2020 1
1 2020 2
1 2020 3
1 2020 4
1 2021 1
1 2021 2
1 2021 3
1 2021 4
;
run

---