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

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

venka — Wed, 17 Sep 2014 15:19:41 GMT

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

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

RW9 — Wed, 17 Sep 2014 15:22:51 GMT

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

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

venka — Wed, 17 Sep 2014 15:48:16 GMT

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.

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

ballardw — Wed, 17 Sep 2014 19:02:49 GMT

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.

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

RW9 — Wed, 17 Sep 2014 19:51:54 GMT

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?

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

venka — Wed, 17 Sep 2014 20:53:50 GMT

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

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

venka — Wed, 17 Sep 2014 20:58:01 GMT

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

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

RW9 — Thu, 18 Sep 2014 07:53:45 GMT

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?

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

venka — Thu, 18 Sep 2014 08:42:42 GMT

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

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

RW9 — Thu, 18 Sep 2014 09:10:17 GMT

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;

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

venka — Thu, 18 Sep 2014 09:59:31 GMT

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

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

Loko — Thu, 18 Sep 2014 10:00:59 GMT

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)

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

RW9 — Thu, 18 Sep 2014 10:17:24 GMT

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.

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

venka — Thu, 18 Sep 2014 10:41:12 GMT

@Loko works perfect, thanks a lot!