topic Re: create variable by using array in SAS Programming

create variable by using array

dht115 — Thu, 30 Dec 2021 15:25:23 GMT

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:

Re: create variable by using array

PaigeMiller — Thu, 30 Dec 2021 15:41:50 GMT

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;

Re: create variable by using array

dht115 — Thu, 30 Dec 2021 15:45:29 GMT

This will create all future qtr based on the date.

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

Re: create variable by using array

PaigeMiller — Thu, 30 Dec 2021 15:51:36 GMT

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?

Re: create variable by using array

dht115 — Thu, 30 Dec 2021 16:24:15 GMT

I need to use historical data to calculate forecast.

Re: create variable by using array

Reeza — Thu, 30 Dec 2021 17:10:16 GMT

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.

Re: create variable by using array

ballardw — Thu, 30 Dec 2021 18:10:16 GMT

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

Re: create variable by using array

Tom — Thu, 30 Dec 2021 18:54:50 GMT

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?

Re: create variable by using array

Reeza — Thu, 30 Dec 2021 19:03:35 GMT

*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:

Re: create variable by using array

PaigeMiller — Thu, 30 Dec 2021 21:30:18 GMT

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