BookmarkSubscribeRSS Feed
scrapex87
Fluorite | Level 6

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;

 

 

9 REPLIES 9
Tom
Super User Tom
Super User

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
Fluorite | Level 6

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
Super User

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
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Super User

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

SYMGET is not the correct approach, VVALUEX will work. 

scrapex87
Fluorite | Level 6

vvaluex did the trick. Thank you. 

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3237 views
  • 0 likes
  • 4 in conversation