Reference all variables whose names start with the same prefix that is numeric

[GeorgeBonanza]

I have a dataset that has month values in 20YYMM format as variable names that I want to reference using an array.  I know that if the variable names started with a common character string such as "COL" then I can reference them using COL: but 20: doesn't seem to work.  The dates will change so the variable reference needs to be dynamic. I can't use the _numeric_ keyword since there are other numeric variables.  Below is a sample dataset and you can see that the last data step causes an error.  Any insight is appreciated.

options validvarname=any;

data work.DATA;
	input PRODUCT MONTH $ AMT;
	datalines;
1 202201 100
1 202202 175
2 202202 200
;
run;

proc transpose
	data= work.DATA
	out=  work.HAVE (drop= _NAME_);
	id MONTH; by PRODUCT; var AMT;
run;

data work.WANT;
	set work.HAVE;
	array MONTHS 20:;

	do over MONTHS;
	/* some stuff */
	end;
run;

[maguiremq]

Is there a reason you're using `options validvarname = any`? If you don't use that, SAS will prepend it with an underscore. You can then use that pattern to do whatever you need to do in your DATA step.

options validvarname=v7;

data work.DATA;
	input PRODUCT MONTH $ AMT;
	datalines;
1 202201 100
1 202202 175
2 202202 200
;
run;

proc transpose
	data= work.DATA
	out=  work.HAVE (drop= _NAME_);
	id MONTH; by PRODUCT; var AMT;
run;

data work.WANT;
	set work.HAVE;
	array MONTHS [*] _:;

	do i = 1 to dim(MONTHS);
		x = MONTHS[i] + 1;
	end;
run;

If you need to impose the date values as columns, maybe do it after your DATA step? Then write it out? I know certain people prefer to have reports made a certain way, so that may be the case.

options validvarname = any;

data want2;
set want;
run;

[GeorgeBonanza]

The variable names have to be in the form 20YYMM without the "_", not by my choice.
I appreciate your time, thank you for responding.

[Ksharp]

Make a macro variable to contain it .

options validvarname=any;

data work.DATA;
	input PRODUCT MONTH $ AMT;
	datalines;
1 202201 100
1 202202 175
2 202202 200
;
run;

proc transpose
	data= work.DATA
	out=  work.HAVE (drop= _NAME_);
	id MONTH; by PRODUCT; var AMT;
run;

proc sql noprint;
select distinct nliteral(month) into : months separated by ' '
 from data;
quit;

data work.WANT;
	set work.HAVE;
	array MONTHS{*} &months. ;
 put MONTHS{1}=;
 put MONTHS{2}=;

run;

[GeorgeBonanza]

I didn't know about the NLITERAL function. This worked and I will definitely use NLITERAL in future work. Thank you for responding, I appreciate your time.

[Tom · Accepted solution]

To make a variable list that references all variables that start with some prefix use the : wildcard.

data _null_;
  set sashelp.class(obs=3);
  put (a:) (=);
run;

If the variable names (or at least the prefix part) are NOT valid SAS names then use a name literal for the prefix.

data _null_;
  set sashelp.class(obs=3);
  put ('a'n:) (=);
run;

If you really only want to include NUMERIC variables and there are both numeric and character variables whose names start with the prefix then perhaps you can read the dataset in pieces.

data want;
   set have(keep=_numeric_);
   array months '20'n: ;
   set have;
run;

This works because the variable list is evaluated when it is first seen and will only find the variables that have already been defined.

PS To avoid the need for name literals set the system option VALIDVARNAME to V7 instead of ANY. Then procedures like PROC IMPORT or PROC TRANSPOSE will not create invalid variable names to begin with.