Hello,
I have following code and I need to create 19 addtional variables by using today's date (total 20 qtr info) by using 'fyqt1' variable (current qtr info).
Desire output should have Qtr 2-2017 to Qtr 1-2022 result. This should auto change when I run this code in month of Jan or Apr.
DATA have;
format todaydt mmddyy10.;
todaydt= date();
*call symput('todaydt',trim(left(input(todaydt,mmddyy10.))));
fy = catx('/',year(todaydt),year(todaydt)+1);
fyqt = qtr(datepart(todaydt));
calyr = year(todaydt);
calqt = catx('-',qtr(todaydt),year(todaydt));
fyqt1 = catx('-',qtr(datepart(todaydt)),year(todaydt)+1);
var = cat('QTR-', substr(fyqt1, 1) );
run;
desire result:
This wide arrangement of data that you want is not a good format to use in further programming.
Better you should produce a long data set:
data want;
set have;
do i=0 to 20;
cal_qt=intnx('quarter',todaydt,i,'b');
output;
end;
format cal_qt yyq6.;
run;
This will create all future qtr based on the date.
I also need to create variable name - not the observation.
well, you may think you need columns with quarters as variable names, I do not and I propose a programming method that will be easier. Any programming you would do with quarters as variable names will be quite difficult.
What use will you make of variables whose name is quarters? What is the next analysis? Are you performing additional calculations, or creating a report output, or what?
I need to use historical data to calculate forecast.
@dht115 wrote:
I need to use historical data to calculate forecast.
If you are going to do the forecast in SAS then you likely actually want a SAS date value and not some odd value as the timeseries procedures generally want an actual date and not something like "Qtr-1-2022" or "4-2021"
You almost certainly do not want to use the DATEPART function on a DATE value. Datepart is intended to extract the date from a DATETIME value which is measured in seconds and Dates are measured in days in SAS. So applying datapart to many recent date values results in a value corresponding to something in the 1960s.
Time series forecasting is done with long data sets, not wide data sets
Example: https://documentation.sas.com/doc/en/pgmmvacdc/9.4/etsug/etsug_code_ariex02.htm
Where is the DATA for these new variables supposed to come from?
Try something like this.
data want ;
today=date();
do offset=-19 to 0;
date=intnx('qtr',today,offset);
string=cat('QTR',put(date,yyqd6.));
output;
end;
format today date date9.;
run;
Result
Obs today offset date string 1 30DEC2021 -19 01JAN2017 QTR2017-1 2 30DEC2021 -18 01APR2017 QTR2017-2 3 30DEC2021 -17 01JUL2017 QTR2017-3 4 30DEC2021 -16 01OCT2017 QTR2017-4 5 30DEC2021 -15 01JAN2018 QTR2018-1 6 30DEC2021 -14 01APR2018 QTR2018-2 7 30DEC2021 -13 01JUL2018 QTR2018-3 8 30DEC2021 -12 01OCT2018 QTR2018-4 9 30DEC2021 -11 01JAN2019 QTR2019-1 10 30DEC2021 -10 01APR2019 QTR2019-2 11 30DEC2021 -9 01JUL2019 QTR2019-3 12 30DEC2021 -8 01OCT2019 QTR2019-4 13 30DEC2021 -7 01JAN2020 QTR2020-1 14 30DEC2021 -6 01APR2020 QTR2020-2 15 30DEC2021 -5 01JUL2020 QTR2020-3 16 30DEC2021 -4 01OCT2020 QTR2020-4 17 30DEC2021 -3 01JAN2021 QTR2021-1 18 30DEC2021 -2 01APR2021 QTR2021-2 19 30DEC2021 -1 01JUL2021 QTR2021-3 20 30DEC2021 0 01OCT2021 QTR2021-4
What are you going to do with these strings when you have them?
Do you want to use PROC TRANSPOSE to use the strings as names of the generated variables?
What values should the variables contain?
*create data set with dates;
data dates;
*start today;
today=date();
*loop through for next 20 periods;
do offset=-19 to 0;
*get date;
date=intnx('qtr', today, offset);
*create label for date;
label=cat('QTR ', put(date, qtr.), ' - ', put(date, year4.));
*create custom variable name;
var_name=cat('Y', put(date, year4.), '_Q', put(date, qtr.));
*create fake variable for transposing;
var=1;
*write to output data set;
output;
end;
format today date date9.;
run;
*flip to wide structure;
proc transpose data=dates /*input data set*/
out=want /*output data set*/;
*specify variable name;
id var_name;
*specify variable label;
idlabel label;
*specify variable value;
var var;
run;
*show output with variable labels;
title 'With labels';
proc print data=want label;
run;
*show output without variable labels;
title 'Without labels';
proc print data=want;
run;
@dht115 wrote:
Hello,
I have following code and I need to create 19 addtional variables by using today's date (total 20 qtr info) by using 'fyqt1' variable (current qtr info).
Desire output should have Qtr 2-2017 to Qtr 1-2022 result. This should auto change when I run this code in month of Jan or Apr.
DATA have; format todaydt mmddyy10.; todaydt= date(); *call symput('todaydt',trim(left(input(todaydt,mmddyy10.)))); fy = catx('/',year(todaydt),year(todaydt)+1); fyqt = qtr(datepart(todaydt)); calyr = year(todaydt); calqt = catx('-',qtr(todaydt),year(todaydt)); fyqt1 = catx('-',qtr(datepart(todaydt)),year(todaydt)+1); var = cat('QTR-', substr(fyqt1, 1) ); run;
desire result:
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.