BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csfcgua
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Astounding
PROC Star

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

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
csfcgua
Calcite | Level 5

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

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 1511 views
  • 7 likes
  • 5 in conversation