SAS code to work in pre-defined variables list

[Ronein]

Hello

I have a data set that contain many variables.

I need to perform calculation on this data set for multiple variables and then check matching to other data set source.

Please note that the variables list on which I should perform the match check is dynamic and defined by other people.

In order to make my code more clever I would like to ask some questions please-

The questions are also written in the SAS code below.

Question1-

If user define a macro variable called V that contain multiple words with comma between them.

How can I define another macro var called V2 with space between them?

(The target is that macro var V2 be calculated automatically from macro var V)

Question2

I perform proc summary for multiple variables with SUM statistics.

What is the way that all calculated statistics have name with prefix _ 

for example: _Weight _Height _age  .

Please note that since I didn't know to do it via proc summary then I used another code to rename variables but here I didnt know to exclude variable sex from the rename

proc sql noprint;
select catx('=',name,catt("_",name))
into :rename_list
separated by ' '
from sashelp.vcolumn
where libname ="WORK" and memname ="CALC_TBL" and name ne 'SEX';
quit;
%put &rename_list;

Question4:

How can I use array to calculate the following ?

Please note that I want that the array work on the defined variables (Macro variable V)

data check_match;
set want;
Check_age=(age ne _age);
check_height=(round(height) ne Round(_height));
check_weight=(round(weight) ne Round(_weight));
Run;

Question5-

In the final proc summary I want that the var statement work on the defined variables (macro var V)

proc summary data=check_match SUM nway;
var Check_age check_height check_weight; /***Question5- how to add the variable list automatically****/
output out=summary(drop=_:) SUM= ;
run;

Data class;
set sashelp.class;
IF age>=15 and age<=20 then sex='Male';else sex='Female';
Run;


Data other_tbl;
input sex $ age height weight ;
cards;
F 119 811 800
M 134 1089.5 639.1
;
Run;

%let V=age,weight,height;

proc sql;
create table tbl1 as
select sex,&V.
from  sashelp.class
;
quit;

%let V2=age weight height; /*Question1-How to calculate macro variable V2 automatyically from given macro variable V1*******/

proc summary data=tbl1 SUM nway;
var &V2.;
class sex;
output out=calc_tbl(drop=_:) SUM= ;
run;
/*Question2-How to rename the calculated variables by adding underscore prefix:_Age _Weight _Height****/


proc sql noprint;
select catx('=',name,catt("_",name))
into :rename_list
separated by ' '
from sashelp.vcolumn
where libname ="WORK" and memname ="CALC_TBL" and name ne 'SEX';
quit;
%put &rename_list;
/***Question3-I want to add prefix to all variables without SEX***/
/***How should I exclude var Sex from the rename***/
/***Add prefix _ to all variables****/

proc datasets library=WORK nolist;
modify calc_tbl;
rename &rename_list;
quit;



proc sort data=calc_tbl(Rename=(_sex=sex));by sex;Run;
proc sort data=other_tbl;by sex;Run;

Data want;
merge other_tbl(in=a)calc_tbl(in=b);
by sex;
if a;
Run;

data check_match;
set want;
Check_age=(age ne _age);
check_height=(round(height) ne Round(_height));
check_weight=(round(weight) ne Round(_weight));
/**Question4-How to write it via array***/
Run;



proc summary data=check_match SUM nway;
var  Check_age check_height check_weight; /***Question5- how to add the variable list automatically****/
output out=summary(drop=_:) SUM= ;
run;

Data class;
set sashelp.class;
IF age>=15 and age<=20 then sex='Male';else sex='Female';
Run;


Data other_tbl;
input sex $ age height weight ;
cards;
F 119 811 800
M 134 1089.5 639.1
;
Run;

%let V=age,weight,height;

proc sql;
create table tbl1 as
select sex,&V.
from  sashelp.class
;
quit;

%let V2=age weight height; /*Question1-How to calculate macro variable V2 automatyically from given macro variable V1*******/

proc summary data=tbl1 SUM nway;
var &V2.;
class sex;
output out=calc_tbl(drop=_:) SUM= ;
run;
/*Question2-How to rename the calculated variables by adding underscore prefix:_Age _Weight _Height****/


proc sql noprint;
select catx('=',name,catt("_",name))
into :rename_list
separated by ' '
from sashelp.vcolumn
where libname ="WORK" and memname ="CALC_TBL" and name ne 'SEX';
quit;
%put &rename_list;
/***Question3-I want to add prefix to all variables without SEX***/
/***How should I exclude var Sex from the rename***/
/***Add prefix _ to all variables****/

proc datasets library=WORK nolist;
modify calc_tbl;
rename &rename_list;
quit;



proc sort data=calc_tbl(Rename=(_sex=sex));by sex;Run;
proc sort data=other_tbl;by sex;Run;

Data want;
merge other_tbl(in=a)calc_tbl(in=b);
by sex;
if a;
Run;

data check_match;
set want;
Check_age=(age ne _age);
check_height=(round(height) ne Round(_height));
check_weight=(round(weight) ne Round(_weight));
/**Question4-How to write it via array***/
Run;



proc summary data=check_match SUM nway;
var  Check_age check_height check_weight; /***Question5- how to add the variable list automatically****/
output out=summary(drop=_:) SUM= ;
run;

[PaigeMiller]

I don't have time right now to answer all of your questions, but here are some answers:

Question 1: use %sysfunc(translate( )), or use the TRANSLATE function in a DATA step; but why not just tell the user to put blanks instead of commas?

Question 2: SUMMARY will not add prefixes, only suffixes which are the name of the statistic requested. You can do bulk renames using PROC SQL as you showed

Question 3: use the WHERE statement in PROC SQL to exclude the variable sex

[PaigeMiller]

I'm back, here are more answers

Question 4: 

You will need to create another macro variable where the underscore is placed before the variable name, let's call this &v2u; and you will need to create another macro variable called &v3 where the check_ is placed before the variable name.

data check_match;
set want;
array v2 &v2;
array v2u &v2u;
array v3 &v3;
do i=1 to dim(v2);
    v3(i)=(round(v2(i))-round(v2u(i));
end;
run;

Question 5:

var &v2;

[ballardw]

Question1-

If user define a macro variable called V that contain multiple words with comma between them.

How can I define another macro var called V2 with space between them?

(The target is that macro var V2 be calculated automatically from macro var V)

Commas inside macro variables may be the poorest choice of delimiters available since the macro language uses the comma to delimit parameters you may end up spending more time fixing the commas than worth while.

WHERE are you actually using the commas as a desirable delimiter. Not in a VAR statement in any procedure. Not in a list of values with the IN operator, not in a keep or drop, not in an array definition. So why even bother having the comma in the first place. If you were doing a Select list in proc SQL that requires commas to delimit that would be another thing but likely complicated by wanting a table alias as well. So I don't see why starting with a comma delimited list is important. Unless someone made a decision without considering where this list is to be used.

Question4:

How can I use array to calculate the following ?

Please note that I want that the array work on the defined variables (Macro variable V)

data check_match;
set want;
Check_age=(age ne _age);
check_height=(round(height) ne Round(_height));
check_weight=(round(weight) ne Round(_weight));
Run;

How do you tell your process that variables age and _age are not rounded before the comparison? Are there other variables than Age that might not get rounded for this comparison? How many? How does the data tell the process that this variable gets rounded and this one doesn't?

Which brings up a question of just how sure are you that you want to  round to an integer value for comparison? What is the real purpose of this comparison of rounded values? Without knowing why I wouldn't suggest a how.

I'm going to go out on a limb a say some of this smells a lot like a limited replacement for Proc Compare.