DATA Step, Macro, Functions and more

Using a macro variable to compute

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Using a macro variable to compute

[ Edited ]

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.

 

 


Error msg.png

Accepted Solutions
Solution
‎06-20-2016 10:31 PM
Super User
Posts: 5,084

Re: Using a macro variable to comput

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.

View solution in original post


All Replies
Solution
‎06-20-2016 10:31 PM
Super User
Posts: 5,084

Re: Using a macro variable to comput

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.

Respected Advisor
Posts: 4,651

Re: Using a macro variable to comput

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;
PG
New Contributor
Posts: 4

Re: Using a macro variable to comput

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

Super User
Super User
Posts: 6,500

Re: Using a macro variable to comput

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;
Super User
Posts: 9,681

Re: Using a macro variable to comput

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 448 views
  • 7 likes
  • 5 in conversation