I could think of following, but how to do it better?
data test;
a=1;
b='abc def';
output;
run;
%macro calc_var;
%let var1 = a;
%let var2 = b;
%let dsid = %sysfunc(open(test));
%let check1 = %sysfunc(varnum(&dsid,&var1));
%let check2 = %sysfunc(varnum(&dsid,&var2));
%let rc=%sysfunc(close(&dsid));
data test2;
set test;
%if &check1>=1 %then
%do;
&var1 = &var1*2;
%end;
%if &check2>=1 %then
%do;
&var2 = compress(&var2,'');
%end; run;
%mend;
%calc_var
Hello,
The following macro may serve your needs:
/*prepare some data*/
data have1;
set sashelp.class;
a+1;
run;
data have2;
set sashelp.class;
b='rete';
run;
data have3;
set sashelp.class;
a+1;
b='rete';
run;
%macro test (dbase, vars=a b, library=WORK);
%let i=1;
%Do %until(%scan(&vars,%eval(&i))=) ;
%let J=1;
%Do %until(%scan(&dbase,%eval(&j))=) ;
%let varname=;%* clear the variable;
proc sql;
select name into :varname from sashelp.vcolumn
where libname="&library" and name="
%scan(&vars,%eval(&i))"
and memname="%scan(&dbase,%eval(&j))";
quit;
%if &varname=a %then %do;
data %scan(&dbase,%eval(&j));
set %scan(&dbase,%eval(&j));
a=a*2;
run;
%end;
%if &varname=b %then %do;
data %scan(&dbase,%eval(&j));
set %scan(&dbase,%eval(&j));
b=compress(b);
run;
%end;
%let j=%eval(&j + 1);
%end;
%let i=%eval(&i + 1);
%end;
%mend test;
/*call the dbases with capital letters*/
%test (HAVE1 HAVE2 HAVE3)
Examine the dataset SASHELP.VCOLUMNS. This details which datasets are present in which library and show all variables, observations etc.
Yes, you are right,
but I also would like to format those columns in the table, like I specified in the macro,
and the macro works, but I would like to optimise the macro.
You don't want to use "format" when talking about SAS variable manipulation such as addition, multiplication or string functions as FORMAT has a very specific meaning related to display of values in SAS. It took me awhile to figure out your code because I was looking for a Format statement.
@ballardw sorry if I have confused you, but what I am basically looking for is optimising the above macro, where
I run through variables of datasets, and see if variables a or b exist, if a exists then mulitiply by 2 and if b exists compress it.
Sorry, I am not seeing what it is you are trying to achieve. It doesn't really make sense. You could get the same result as above by:
data _null_;
set sashelp.vcolumn (where=(libname="WORK" and memname="TEST" and name="A" in=a))
sashelp.vcolumn (where=(libname="WORK" and memname="TEST" and name="B" in=b));
if a then call execute('data work.test; set work.test; a=a * 2; run;');
if b then call execute('data work.test; set work.test; b=compress(b,'');run;');
run;
The real point is why do you want to do anything like the above?
@RW9
What I need is scan through columns of a datasets, and see if variables a or b exists, if a exists then mulitiply by 2 and if b exists compress it.
Yes, I can see what the code is doing, the question is why would you need to scan through the columns to see if they exist? Surely at the point of getting to that code you would know they exist, even if in the previous statement you put a length a b statement after the data xxx; so that the variable appears even if it has no data.
So please provide a bit of background information as to why the necessity to scan through columns has come about, is it a transpose for example and you don't know how many columns there are?
@RW9
its because, I need to run through let's day 10 datasets, of which only 2 of the datasets will have the variables a and b.
So, every time I run through a dataset, I need to see if variables a or b exist, if a exists then mulitiply by 2 and if b exists compress it.
Well, still not understanding the why on this, however the following program demonstrates another way of doing it:
/* Some test datasets */
data ex1;
a=123; b="AAA"; output;
run;
data ex2;
a=124; output;
run;
data ex3;
b="XYZ"; output;
run;
/* This generates the necessary code */
data to_do;
set sashelp.vcolumn (where=(libname="WORK" and substr(memname,1,2)="EX" and upcase(name)="A") in=a)
sashelp.vcolumn (where=(libname="WORK" and substr(memname,1,2)="EX" and upcase(name)="B") in=b);
call execute('data work.'||strip(memname)||'; set work.'||strip(memname)||';');
if a then call execute('a=a*2;');
if b then call execute('b=compress(b,'||"''"||');');
call execute('run;');
run;
@RW9, I appreciate your help.
the reason is, to start with I wouldn't know if the dataset will have variable a or b,
f.ex I should be able to handle following two scenarions as well, apart from ex1,ex2,ex3 in your test data.
(at the end I will export all datasets to csv,
so end user will have ex1,ex2,ex3, ex4,another_ex, with corrected values in a,b for ex1,ex2,ex3,another_ex
note: ex4 doesnot have variable a or b.
data ex4;
c=1000; output;
run;
data another_ex;
a=1000; z='xyz'; output;
run;
Did you try my code with those additional two datasets? The first one EX4, should be fine - no code generated as it would have no records in either A or B reference, hence nothing to do on that. As for the second one - another_ex, well, its up to you how to name datasets, however you do need to have some logical method to identify which datasets you want to work with. You could for instance modify this:
set sashelp.vcolumn (where=(libname="WORK" and substr(memname,1,2)="EX" and upcase(name)="A")
To:
set sashelp.vcolumn (where=(libname="WORK" and index(memname,"EX") and upcase(name)="A")
Then it will take any dataset in WORK which has the text EX in it. Or maybe all your datasets are in a library somewhere, then change to:
set sashelp.vcolumn (where=(libname="MY_LIBRARY" and upcase(name)="A")
This would then do for all datasets irrespective of name in the library MY_LIBRARY.
You just need to modify the code to your specific scenario, as I can only run on the info in the post.
Hello,
The following macro may serve your needs:
/*prepare some data*/
data have1;
set sashelp.class;
a+1;
run;
data have2;
set sashelp.class;
b='rete';
run;
data have3;
set sashelp.class;
a+1;
b='rete';
run;
%macro test (dbase, vars=a b, library=WORK);
%let i=1;
%Do %until(%scan(&vars,%eval(&i))=) ;
%let J=1;
%Do %until(%scan(&dbase,%eval(&j))=) ;
%let varname=;%* clear the variable;
proc sql;
select name into :varname from sashelp.vcolumn
where libname="&library" and name="
%scan(&vars,%eval(&i))"
and memname="%scan(&dbase,%eval(&j))";
quit;
%if &varname=a %then %do;
data %scan(&dbase,%eval(&j));
set %scan(&dbase,%eval(&j));
a=a*2;
run;
%end;
%if &varname=b %then %do;
data %scan(&dbase,%eval(&j));
set %scan(&dbase,%eval(&j));
b=compress(b);
run;
%end;
%let j=%eval(&j + 1);
%end;
%let i=%eval(&i + 1);
%end;
%mend test;
/*call the dbases with capital letters*/
%test (HAVE1 HAVE2 HAVE3)
@Loko works perfect, thanks a lot!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.