DATA Step, Macro, Functions and more

Designate variable name with intermediate match

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Designate variable name with intermediate match

When specifying variable name,I'm looking for how to designate variables in the middle match for the variable name.

 

data _test;
 VAR_A_A=1;
 VAR_B_A=2;
 VAR_C_A=3;

 VAR_A_B=100;
 VAR_B_B=200;
 VAR_C_B=300;
 run;
 
 data _null_;
  set _test;
   Total_by_colon=sum(of VAR:);put "Total is " Total_by_colon;
  run;


In this situation, I wanna sum "VAR_A_A" "VAR_B_A" VAR_C_A", which should be ending with answer 6(=1+2+3).

But we know,If we write
sum(of VARSmiley Happy;
This ansiwer is 606(=1+2+3+100+200+300).

606.JPG

 

From a different point of view, I want to use a regular expression when specifying vars. But it will not be.

I think there is a need to create a macro?

 

If you have the wisdom, thank you in advance.
Regards,t_ar_taat


Accepted Solutions
Solution
‎09-11-2016 05:00 AM
Super User
Posts: 10,020

Re: Designate variable name with intermediate match

Posted in reply to t_ar_taat
Yeah. Make a macro variable to hold it.




data _test;
 VAR_A_A=1;
 VAR_B_A=2;
 VAR_C_A=3;

 VAR_A_B=100;
 VAR_B_B=200;
 VAR_C_B=300;
 run;
proc transpose data=_test(obs=0) out=temp;
var _all_;
run;
proc sql;
select _name_ into : list separated by ','
 from temp
  where upcase(_name_) like 'VAR~_%~_A' escape '~';
quit;
data _null_;
  set _test;
   Total_by_colon=sum(&list);put "Total is " Total_by_colon;
  run;


View solution in original post


All Replies
Super User
Posts: 19,770

Re: Designate variable name with intermediate match

Posted in reply to t_ar_taat

You can create a macro or use the VVALUEX logic and obtain the values. 

Contributor
Posts: 35

Re: Designate variable name with intermediate match

Hi,Reeza

Thank you for giving me your knoledge very quickly.I'll try it.

Solution
‎09-11-2016 05:00 AM
Super User
Posts: 10,020

Re: Designate variable name with intermediate match

Posted in reply to t_ar_taat
Yeah. Make a macro variable to hold it.




data _test;
 VAR_A_A=1;
 VAR_B_A=2;
 VAR_C_A=3;

 VAR_A_B=100;
 VAR_B_B=200;
 VAR_C_B=300;
 run;
proc transpose data=_test(obs=0) out=temp;
var _all_;
run;
proc sql;
select _name_ into : list separated by ','
 from temp
  where upcase(_name_) like 'VAR~_%~_A' escape '~';
quit;
data _null_;
  set _test;
   Total_by_colon=sum(&list);put "Total is " Total_by_colon;
  run;


Contributor
Posts: 35

Re: Designate variable name with intermediate match

HI,Xia

Thank you very much.

I confirmed your codes work well,in short,answer is actually "6".6.jpg

 

 

Super User
Posts: 11,343

Re: Designate variable name with intermediate match

Posted in reply to t_ar_taat

Some times picking your variable names may be sufficient. Please look at this:

data _test;
 VAR_A_A=1;
 VAR_A_B=2;
 VAR_A_C=3;

 VAR_B_A=100;
 VAR_B_B=200;
 VAR_B_C=300;

 Sum1 = sum(of Var_A:);
 Sum2 = Sum(of Var_B:);
 run;
Contributor
Posts: 35

Re: Designate variable name with intermediate match

Hi,ballardw

 

Yes,actually the way you showed is also good.

Thank you for your posting.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 384 views
  • 0 likes
  • 4 in conversation