naming of array with year month as subscript

Reply
Contributor
Posts: 36

naming of array with year month as subscript

If I want to name my array with year/month as subscript, can that be done ? Please refer to below thread and my last comment -

If I want to rename my fields as month_cnt_201412, month_cnt_201501 etc.. can it be done in this step?

Even if I have to RENAME it in a subsequent step, can some function be used ? The variable list is going to grow every month e.g. month_cnt_201502,month_cnt_201503,month_cnt_201504 ..

Esteemed Advisor
Posts: 5,198

Re: naming of array with year month as subscript

That thread was quote long, and I feel a bit lazy today, so I ask: do wish to keep these variables, or is there another reason you wish do name them this way?

Usually, "long" tables (many columns/variables) are difficult to use for most query/reporting purposes.

Data never sleeps
Esteemed Advisor
Esteemed Advisor
Posts: 7,211

Re: naming of array with year month as subscript

Not as far as I know, the array is an incremental counter, so 1 2 3 4 etc.  TBH why would you want to do such a thing.  This is not useful in further processing, the principal of using a numeric suffix is to allow the use of lists and lops over arrays.  So my suggestion would be to:

array month_cnt{x};

label month_cnt1 "Dec 2014"

          month_cnt2 "Jan 2015"...

So have the variable name a sequential list of variables - hence you get all the functionality of dealing with lists month_cnt1-month_cntx, but you apply a label which is relevant to the data, i.e. the month and year.  That way you have the best of both worlds and use each part of the item as it was meant.

Contributor
Posts: 36

Re: naming of array with year month as subscript

Thanks RW9.

I agree with your suggestion of labeling, but can I do labeling dynamically through a macro variable ? (as the variable list will continue to grow)

I have posted the answer from Tom.

Esteemed Advisor
Esteemed Advisor
Posts: 7,211

Re: naming of array with year month as subscript

Here is an example below.

data test;
  dte='13feb2015'd; result=5; output;
  dte='10feb2015'd;  result=4; output;
  dte='11feb2015'd;  result=3; output;
  dte='05feb2015'd;  result=8; output;
  dte='08feb2015'd;  result=5; output;
run;

proc sql;
  select  "MONTH_CNT"||strip(put(MONOTONIC(),best.))||"='"||strip(put(DTE,date9.))||"'"
  into    :LBL separated by ' '
  from    WORK.TEST;
quit;

data WANT;
  array month_cnt{5} $5.;
  label &LBL.;
run;

Contributor
Posts: 36

Re: naming of array with year month as subscript

Answer from @Tom

---------

You would need to generate those names into a macro variable.

If there are no gaps in the months then you could generate the names from the unique months in the data.

select distinct catx('_','month',substr(put(tran_date,yymmddn8),1,6),'cnt')

into :cnt_vars separated by ' '

from datefile

;

...

array month &cnt_vars ;

But if there are gaps then you need to generate names of all of the months in the interval (otherwise the calculated index based on number of months since index date will not work).

data _null_;

length cnt_vars $32000 ;

do i=0 to &nmonths-1 ;

   cnt_vars = catx(' ',cnt_vars,catx('_','month',substr(put(tran_date,yymmddn8),1,6),'cnt'));

end;

call symputx('cnt_vars',cnt_vars);

run;

-------------------

The full script looks as follows -

/*create dummy data*/

data datefile;

input tran_date date9. cnt 3.;

datalines;

13feb2015 5

10feb2015 4

11feb2015 3

05feb2015 8

08feb2015 5

01jan2015 1

20dec2014 1

31jan2015 2

23dec2014 2

12jan2015 1

;

/*You would need to generate those names into a macro variable.

If there are no gaps in the months then you could generate the names from the unique months in the data. */

proc sql;

select distinct catx('_','month',substr(put(tran_date,yymmddn8.),1,6),'cnt')

into :cnt_vars separated by ' '

from datefile

;

quit;

proc sql noprint ;

  select min(tran_date),max(tran_date)

    into :mindt,:maxdt

    from datefile

  ;

quit;

%let nmonths=%eval(%sysfunc(intck(month,&mindt,&maxdt))+1);

data want ;

  set datefile ;

  *array month (&nmonths) ;

  array month &cnt_vars ;

  do _n_=1 to &nmonths; month(_n_)=0; end;

  month(intck('month',&mindt,tran_date)+1)=cnt;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 265 views
  • 6 likes
  • 3 in conversation