Help using Base SAS procedures

How to check if a list of columns exists in a dataset and do formatting only on those columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

How to check if a list of columns exists in a dataset and do formatting only on those columns

  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                                                                 


Accepted Solutions
Solution
‎09-18-2014 06:01 AM
Super Contributor
Posts: 305

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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


All Replies
Super User
Super User
Posts: 7,430

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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

Occasional Contributor
Posts: 19

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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.

Super User
Posts: 10,550

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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.

Occasional Contributor
Posts: 19

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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


Super User
Super User
Posts: 7,430

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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?

Occasional Contributor
Posts: 19

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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

Super User
Super User
Posts: 7,430

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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?

Occasional Contributor
Posts: 19

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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

Super User
Super User
Posts: 7,430

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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;

Occasional Contributor
Posts: 19

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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

Super User
Super User
Posts: 7,430

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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.

Solution
‎09-18-2014 06:01 AM
Super Contributor
Posts: 305

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

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)

Occasional Contributor
Posts: 19

Re: How to check if a list of columns exists in a dataset and do formatting only on those columns

@Loko works perfect, thanks a lot!

🔒 This topic is solved and locked.

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

Discussion stats
  • 13 replies
  • 1008 views
  • 8 likes
  • 4 in conversation