How to check for the existence a particular variable based on its value, if the dataset exists?

[jjames1]

Hello,

I am trying to write a condition in order to check the existence a particular variable based on its value if the dataset exist or else come out  of the loop and do something else.

To get a clear picture . Consider the following table City_US_and_Canada 

Country	City	State
US	Albany	NY
US	Baker	OR
US	Austin	TX
Canada	Toronto	ON
Canada	Montreal	QC
Canada	Edmonton	AB

Now in my case first of all I need to check if the table City_US_and_Canada exist. For that I use the following piece of code

%if %sysfunc(exist(City_US_aand_Canada))%then%do

Now if this table exists I need to filter it just for one country say US. My final output should look like

Country	City	State
US	Albany	NY
US	Baker	OR
US	Austin	TX

Please help me to fix this one

Thanks in advance!

[Tom]

You need to give more context to get a complete answer.

data want;
 set City_US_and_Canada;
 where Country="US";
run;

[jjames1]

Thank you for the reply!

I am generating this tables directly from XML files in SAS. In my case I have different XML files. So when I read the xml files ,I need to check if the table is present to do the transformations. Once if the table is present I need to check for the variables based on its values.

This is something that I was looking to do

%if %sysfunc(exist(City_US_aand_Canada where country='US'))%then%do

But this one is not working

[mkeintz]

You apparently want to conditionally execute a DATA step where the step reads the discovered data set and outputs a subset of observations, as in:

%macro t;
  %if %sysfunc(exist(City_US_and_Canada)) %then %do;
    data want;
      set City_US_and_Canada;
      where country='US';
    run;
  %end;
  %else %put City_US_and_Canada dataset not fuond ;
%mend;
options mprint;
%t;

[jjames1]

Thank you for the reply. 

But I am looking for something like 

%if %sysfunc(exist(City_US_aand_Canada where country='US'))%then%do

But it does not seem to work 

Any idea on how to look into this?

[ballardw]

What you are attempting is not going to be a one line of code operation though after you get something working without macro code then something can be done.

Steps would be

1) verify the existence of the dataset

2) verify the existence of the variable in that data set

if both of those conditions are met then you can start looking for specific values.

For instance:

proc sql noprint;
   select *
   from dictionary.columns
   where Libname='SASHELP' and Memname=('CLASS')
   and upcase(name)='SEX'
   ;
quit;

%put &sqlobs;

The select examines you SAS metadata which has libraries (LIBNAME), data sets (MEMNAME) and variables (NAME) plus characteristics of the variables. The macro variable SQLOBS is an automatic variable that contains the number of records returned by the latest Proc SQL query. &sqlobs will be 0 if no records were returned. Note that the Libname and Memname are stored as upper case in the dictionary table, variablenames may be mixed case so be careful on syntax using those.

So you could run that SQL snippet and and then use

%if &sqlobs > 0 %then %do.

If I were doing this frequently might create function or small macro to return the value of sqlobs when passed a library (which could default to WORK) , a data set name and a variable name as parameters. I'll leave that as a small exercise for the intersted reader at this time.