Using a macro variable to compute

[csfcgua]

Hello,

I have a dataset (Data1) like as below. The variable string indicates how to sum up Q1 ~ Q3.

ID Q1 Q2 Q3  string

1   1    3    1       sum(Q1, Q3)

2   3    2    2       sum(Q1, Q2, Q3)

3   2    1    4       sum(Q2, Q3)

Now I would like to create a new numerical variable Score which is sumed as string text:

ID Q1 Q2 Q3  string               Score

1   1    3    1       sum(Q1, Q3)         2

2   3    2    2       sum(Q1, Q2, Q3)  7

3   2    1    4       sum(Q2, Q3)         5

My code:

Data _null_;

set Data1;

call symput('n' !! strip(_n_), string);

call symput('total' , _n_);

Run;

%macro test;

Data compute;

set Data1;

%Do i = 1 %to &total;

Score = &&n&i;

%End;

Run;

%mend;

%test;

But a error message keeps saying "Syntax error". Does anyone know what the problem is? How to modify my code? Thanks.

ps.The log window is attached below.

[Astounding · Accepted solution]

Take a look through your data set for the values of STRING.  It's possible that one of the values doesn't form a valid SAS statement.  In particular, if STRING is blank, this generated statement might result in the message you're seeing (can't test it right now):

Score = ;

At any rate, even if the program ran without error it would generate the wrong answer.  You would get a DATA step that looked like this:

data compute;

set Data1;

Score = sum(Q1, Q3);

Score = sum(Q1, Q2, Q3);

Score = sum(Q2, Q3);

run;

Clearly, if you saw that data step without macro language, you would realize that the logic is incorrect.

Try it this way (again, I can't test it right now):

data compute;

set Data1;

if string > ' ' then score = resolve(string);

run;

If it works, you're home free with no macro complications.

[PGStats]

No need for macro coding. Take advantage of array and function vname:

data want;
set test;
array v Q1--Q3;
do i = 1 to dim(v);
    if findw(string, vname(v{i})) > 0 then score = sum(score, v{i});
    end;
drop i;
run;

proc print noobs; run;

[csfcgua]

@Astounding Thank you. I checked my data and removed the rows with blank string. Everything works now!

@PGStats Your simple way is good. Save me a lot of time. Thanks.

[Tom]

You need to do some code generation. I find it easiest to do this by using a data step to write the code to a file and then using %INCLUDE to run the generated code.

First get the distinct values of STRING and then generate the IF/THEN (or SELECT if you want) statements.  Then run them.

data have ;
  infile cards dsd dlm='|' truncover ;
  length id q1-q3 8 string $30 expected_score 8 ;
  input id -- expected_score ;
cards;
1|1|3|1|sum(Q1, Q3)|2
2|3|2|2|sum(Q1, Q2, Q3)|7
3|2|1|4|sum(Q2, Q3)|5
;;;;

proc sort data=have (keep=string) out=strings nodupkey ;
  by string;
run;

filename code temp;
data _null_;
  set strings ;
  file code;
  if _n_ > 1 then put 'else ' @;
  put 'if ' string = :$quote. 'then score=' string ';' ;
run;

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

[Ksharp]

data have ;
  infile cards dsd dlm='|' truncover ;
  length id q1-q3 8 string $30 expected_score 8 ;
  input id -- expected_score ;
cards;
1|1|3|1|sum(Q1, Q3)|2
2|3|2|2|sum(Q1, Q2, Q3)|7
3|2|1|4|sum(Q2, Q3)|5
;;;;
;
data want;
 set have; 
 array x{*} q1-q3;
 length temp $ 2000;
 temp=string;
 do i=1 to dim(x);
  temp=tranwrd(temp,upcase(vname(x{i})),strip(x{i}));
 end;
 want=resolve(cats('%sysfunc(',temp,')'));
run;