topic Re: Using a macro variable to comput in SAS Programming

Using a macro variable to compute

csfcgua — Wed, 06 Jul 2016 01:56:34 GMT

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.

Re: Using a macro variable to comput

Astounding — Tue, 21 Jun 2016 02:15:07 GMT

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.

Re: Using a macro variable to comput

PGStats — Tue, 21 Jun 2016 02:27:59 GMT

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;

Re: Using a macro variable to comput

csfcgua — Tue, 21 Jun 2016 02:38:54 GMT

@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.

Re: Using a macro variable to comput

Tom — Tue, 21 Jun 2016 02:42:43 GMT

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;

Re: Using a macro variable to comput

Ksharp — Tue, 21 Jun 2016 03:17:09 GMT

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;