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

  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                                                                 

1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

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)

View solution in original post

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Examine the dataset SASHELP.VCOLUMNS.  This details which datasets are present in which library and show all variables, observations etc.

venka
Calcite | Level 5

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.

ballardw
Super User

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.

venka
Calcite | Level 5

@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.


RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

venka
Calcite | Level 5

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

venka
Calcite | Level 5

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

venka
Calcite | Level 5

@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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Loko
Barite | Level 11

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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 13 replies
  • 7377 views
  • 8 likes
  • 4 in conversation