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.
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.
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.
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;
@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.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.