BookmarkSubscribeRSS Feed
dht115
Calcite | Level 5

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;

 

dht115_0-1640876142653.png

desire result: 

dht115_1-1640877715220.png

 

 

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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;
        

 

--
Paige Miller
dht115
Calcite | Level 5

This will create all future qtr based on the date. 

I also need to create variable name - not the observation. 

PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
dht115
Calcite | Level 5

I need to use historical data to calculate forecast. 

Reeza
Super User
Transpose your long format to a wide format is the easiest way to do this anyways dynamically. Otherwise it's a pain to get the right variable names. As others have indicated though, it's better to have your data in a long format not a wide format but ultimately your choice anyways so use PROC TRANSPOSE to flip Paige's output to a wide format. It's fully dynamic and will scale to whatever quarter or months you want.

Typing out those variable names in code alone would make me cringe at that data structure. I'd recommend using a format to display the variables like that but to use a cleaner name that's easier to type out, but that's my opinion. You can use the LABEL statement on PROC TRANSPOSE to assign a different label to the variable than the variable name.
ballardw
Super User

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

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Tom
Super User Tom
Super User

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?

 

Reeza
Super User
*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;

 

dht115_0-1640876142653.png

desire result: 

dht115_1-1640877715220.png

 

 


 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1804 views
  • 2 likes
  • 5 in conversation