BookmarkSubscribeRSS Feed
jjames1
Fluorite | Level 6

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!

5 REPLIES 5
Tom
Super User Tom
Super User

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

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

 

jjames1
Fluorite | Level 6

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
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
jjames1
Fluorite | Level 6

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
Super User

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1280 views
  • 1 like
  • 4 in conversation