BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jumboshrimps
Obsidian | Level 7

Receiving datasets from a variety of clients on a Redhat Enterprise Server 7 (Maipo)

Need to check if the required 15 fields are present in the dataset, if not 

then send message back to calling BASH program that the dataset submitted doesn't

"pass muster", so to speak. 

No further error handling is required, such as what fields are missing,etc,  at this point.

 

Using simple create table proc sql to create a tmp dataset consisting of  fields 1 - 15 from the submitted dataset.

If any one field of the fifteen is missing, PROC SQL throws an error,(obviously), although the 

NOERRORSTOP optiion will allow the code to continue to execute, (to check any other datasets submitted along with the first one). 

My boss REALLY doesn't want any errors in the log, even if I'm later checking to see if  tmp datasets 1,2,3, etc. exist.  If  just one of the tmp tables weren't created by the proc sql create query, the code sends an error message back to be collected by the BASH shell script.

I'm thinking PROC SQL is not the answer to determine if a dataset has a full complement of variables (but not what is IN the variables).  If more variables are in the dataset than required, they will NOT be included, they just drop into the ether.  How else can I check multiple datasets for a full complement of variables?

 

Code is really simple -

Proc SQL;

create table DS1 as select

field1 field2, field3,(etc).

from dataset;

quit;

 

 

THANX.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

This is the sort of thing that I would delegate to my program that reads the data.

Just how do you read the file into SAS?

 

If you don't have to do do more than indicate that there are not 15 fields then perhaps what might work better would be to read the number of columns that exist in the data set. SAS has, accessible in Proc SQL, DICTIONARY.COLUMNS that has the name and properties of every variable in every data set in all of the currently defined libraries. So you could look for the existence of specific variables with something similar to:

Proc sql;
   create table set1count as
   select count(*) 
   from dictionary.columns 
   where libname='WORK' and memname='DATASET1'
      and upcase(name) in ('FIELD1' 'FIELD2' 'FIELD3' ...)
   ;
run;

library name and member name (data set name) are stored in upper case in the table. Name of variable is not so it may be a good idea to make sure your comparison is not case sensitive. The count would indicate how many of the variables in the list (I only did 3 out of laziness) were found. There are a number of ways to capture the information and use it. If paranoid enough you could check DICTIONARY.TABLES that MEMNAME exists as well.

View solution in original post

1 REPLY 1
ballardw
Super User

This is the sort of thing that I would delegate to my program that reads the data.

Just how do you read the file into SAS?

 

If you don't have to do do more than indicate that there are not 15 fields then perhaps what might work better would be to read the number of columns that exist in the data set. SAS has, accessible in Proc SQL, DICTIONARY.COLUMNS that has the name and properties of every variable in every data set in all of the currently defined libraries. So you could look for the existence of specific variables with something similar to:

Proc sql;
   create table set1count as
   select count(*) 
   from dictionary.columns 
   where libname='WORK' and memname='DATASET1'
      and upcase(name) in ('FIELD1' 'FIELD2' 'FIELD3' ...)
   ;
run;

library name and member name (data set name) are stored in upper case in the table. Name of variable is not so it may be a good idea to make sure your comparison is not case sensitive. The count would indicate how many of the variables in the list (I only did 3 out of laziness) were found. There are a number of ways to capture the information and use it. If paranoid enough you could check DICTIONARY.TABLES that MEMNAME exists as well.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 791 views
  • 0 likes
  • 2 in conversation