SCAN function call has too many arguments in dataset

[scrapex87]

Hi, 

I am having an issue separating a string with a particular delimiter. I keep getting this error

"The SCAN function call has too many arguments." 

&RankGroupPeriod - is created by reading from a dataset and looks like "CR_Rank,QR_Rank,WC_Rank+FDR_Rank,BQ_Rank,NCD_Rank+ ..etc..."

I guess the value of &RankGroupPeriod is too large for scan function. Is there another way to separte &RankGroupPeriod by + and create a column in the dataset for each of the values ?

proc sql noprint; 
 select comb into :RankGroupPeriod separated by '+'
 from testcombination;
quit;

proc sql noprint; 
 select count(comb) into :countCombinations
 from testcombination;
quit;

%put RankGroupPeriod = &RankGroupPeriod;/*list of comma separated groups of 3 rankGroups*/
%put countCombinations = &countCombinations;/* number of combinations in table*/

data comp4a; 
	  set comp4;
	  by year gvkey;
	  do i = 1 to &countCombinations;
			/*get values from list,one by one, separtated by '.' */
			/* each value would look like this CR_Rank,QR_Rank,WC_Rank */
		    value=scan(&RankGroupPeriod,i,'+');
			output;
	  end;
run;

[Tom]

When SAS macro generates code the generated code is passed to regular SAS for processing.  So you need to generate code that SAS can process.

In SAS code a string literal is enclosed in quotes.

value=scan("&RankGroupPeriod",i,'+');

Or you could change to using the SYMGET() function instead.

value=scan(symget('RankGroupPeriod'),i,'+');

[scrapex87]

Thank you Tom. That fixed the issue. But, now I am getting the following if I pass the value in a mean function. 

I guess Scan is returning a numeric data and i'm mean function is expecting a string. 

NOTE: Invalid numeric data, value='ROA_Rank,WC_Rank,NM_Rank' , at line 3554 column 20.

data comp4a; 
	  set comp4;
	  by year gvkey;
	  do i = 1 to &countCombinations;
			/*get values from list,one by one, separtated by '.' */
			/* each value would look like this CR_Rank,QR_Rank,WC_Rank */
		    value=scan(symget('RankGroupPeriod'),i,'+');
			AggRank= mean(value);
			output;
	  end;
run;

[Reeza]

Value is a character variable. You either need to convert this to a numeric or change your logic - I'm not sure what you're trying to do but I suspect it's being over complicated. 

[art297]

As @Tom mentioned, you need to enclose the macro variable in quotes. Is there a limit to macro variable size? YES. Approximately 64K.

If your list contains more characters than that you need an alternative solution.

How do you plan to use it .. what are you trying to do? There are always alternatives, but one can't suggest them without knowing the papameters and goal(s).

Art, CEO, AnalystFinder.com

[scrapex87]

The ultimate goal is to compute the mean/average of a list of columns that is being defined by the "RankGroupPeriod" variable. 

The RankGroupPeriod looks like this "CR_Rank,QR_Rank,WC_Rank+FDR_Rank,BQ_Rank,NCD_Rank+ ..etc..."

The comma sepparated values are column names in the dataset "comp4". So, for each 3 of those columns, I want to compute the average value and put it in a new column.

So, for each iteration the value would be "CR_Rank,QR_Rank,WC_Rank".

If you put that value into the mean(CR_Rank,QR_Rank,WC_Rank) function it should give the average for those columns and create a new column for each iteration I.E. AggRank_CR_Rank,QR_Rank,WC_Rank. 

Not sure if there is another way of doing it.

data comp4a; 
	  set comp4;
	  by year gvkey;
	  do i = 1 to &countCombinations;
			/*get values from list,one by one, separtated by '.' */
			/* each value would look like this CR_Rank,QR_Rank,WC_Rank */
		    value=scan(symget('RankGroupPeriod'),i,'+');
			AggRank_value= mean(value);/*name the column based on the value*/
			output;
	  end;
run;

[art297]

Sounds to me like you want proc means, proc summary or proc sql. You can use a by statement with the first two, and a group by with proc sql.

Do you just want the means, or do you want them attached to each record in your data set?

Art, CEO, AnalystFinder.com

[Reeza]

Then the same method used in your previous question is applicable. 

SYMGET is not the correct approach, VVALUEX will work. 

[scrapex87]

vvaluex did the trick. Thank you. 

[Tom]

If you want to take the MEAN() of variables then you need to use the MEAN() function and not use a text function like SCAN().

If I understand it right you have the following two macro variables.

%let list=CR_Rank,QR_Rank,WC_Rank+FDR_Rank,BQ_Rank,NCD_Rank;
%let n=2;

And since N=2 you want to calculate two means. This means you need to generate two MEAN() function calls.

value = mean(CR_Rank,QR_Rank,WC_Rank);
...
value = mean(FDR_Rank,BQ_Rank,NCD_Rank);

You could do it with a MACRO %DO loop, but you can't do it with a data step DO loop.

Another way to generate code is to just write it to a file and use %INCLUDE where you want to run it.

So if your metadata looks like:

data metadata;
  input varlist $80.;
cards;
CR_Rank,QR_Rank,WC_Rank
FDR_Rank,BQ_Rank,NCD_Rank
;

You could use a simple data step to write the code you need.

filename code temp;
data _null_;
  set metadata;
  put 'value=mean(' varlist ');'
    / 'output;'
  ;
run;

You could then use that code later to process your data.

data want ;
  set have ;
%include code / source2;
run;

In general it is much easier to work with dataset and text files than macro variables and macro logic. 

Probably your metadata looks more like (or should look like this).

data metadata;
  input group varname $32.;
cards;
1 CR_Rank
1 QR_Rank
1 WC_Rank
2 FDR_Rank
2 BQ_Rank
2 NCD_Rank
;

Then your code generation step would look more like this.

filename code temp;
data _null_;
  set metadata;
  by group;
  if first.group then put group= ';' / 'value=mean(.' @ ;
  put ',' varname @;
  if last.group then put ');' / 'output;' ;
run;

Then the groups could have different numbers of variables and you would get code like:

group=1;
value = mean(.,CR_Rank,QR_Rank,WC_Rank);
output;
group=2;
value = mean(.,FDR_Rank,BQ_Rank,NCD_Rank);
output;