topic Re: Dynamically create Fiscal Year and Quarters variable for repeated ids in SAS Programming

Dynamically create Fiscal Year and Quarters variable for repeated ids

Paakay — Wed, 22 Dec 2021 21:21:07 GMT

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

Re: Dynamically create Fiscal Year and Quarters variable for repeated ids

PaigeMiller — Wed, 22 Dec 2021 21:35:02 GMT

@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;

Re: Dynamically create Fiscal Year and Quarters variable for repeated ids

Reeza — Wed, 22 Dec 2021 22:45:40 GMT

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