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

Reply
Contributor
Posts: 34

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

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!

Super User
Super User
Posts: 6,498

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

[ Edited ]

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

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

 

Contributor
Posts: 34

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

[ Edited ]

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

Valued Guide
Posts: 797

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

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;
Contributor
Posts: 34

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

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?

Super User
Posts: 10,472

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

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.

Ask a Question
Discussion stats
  • 5 replies
  • 126 views
  • 1 like
  • 4 in conversation