DATA Step, Macro, Functions and more

Check for non-existing data element in a file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Check for non-existing data element in a file

I have several files with the same data elements.

However, in some of these files a prior programmer, omitted some data elements.

Example:   Id, Name, Test1, Test2, Test3, GREVERB, GREVERB2, GMATVERB

some files are missing:  GREVERB, GREVERB2

 

Is there anyway to test that these data elements are missing?


Accepted Solutions
Solution
‎09-13-2016 12:18 PM
Super User
Posts: 19,822

Re: Check for non-existing data element in a file

Posted in reply to IsoTropic

Take a ook at sashelp.vcolumn which lists all columns in all tables. 

 

You can run a proc freq by column name to see variable frequency. And can query the table to filter the for tables that are missing the variables of interest.

 

View solution in original post


All Replies
Solution
‎09-13-2016 12:18 PM
Super User
Posts: 19,822

Re: Check for non-existing data element in a file

Posted in reply to IsoTropic

Take a ook at sashelp.vcolumn which lists all columns in all tables. 

 

You can run a proc freq by column name to see variable frequency. And can query the table to filter the for tables that are missing the variables of interest.

 

Occasional Contributor
Posts: 9

Re: Check for non-existing data element in a file

I actually know what files are missing the values.
If I did not this would work well.
I will just need to recreate the files with the correct variables.
Super User
Super User
Posts: 7,976

Re: Check for non-existing data element in a file

Posted in reply to IsoTropic

Define "files".  Do you mean text files, excel files, SAS datafiles etc.

 

Personally I find this to be a defect in the import process.  A clean process would be:

1) Define Data Transfer Specifications document - this is the basis for everything next and should be agreed and approved by both parties.

2) Create an import progra/check program based on that document

3) Test the process with test data

4) After pass, the document is live and transfer can occur.

5) At each delivery data is checked/imported/validated etc. per the document.  Any failures go directly back to the supplier.

 

It sounds like you don't have this agreement, hence you really don't have much control over what is happening.  I mean what if they decide to include GREVERB, but not TEST1, or maybe they call all the variables XYZ1-XYZ10, or they just write the information on the back of a cornflakes box, scan that in, and send it someone else?  A bit of an extreme example, but the point is the process is everything, and should be thoroughly documented, tested, replicable before and at each time.

Super User
Posts: 11,343

Re: Check for non-existing data element in a file

Posted in reply to IsoTropic

Here is an approach using a control data set with the information, library, dataset and variable name of interest, and searching dictionary.columns (or sashelp.vcolumn) to create a data set with the information and an added variable "found" which will have a value of 1 when found (exists) or 0 when it doesn't.

 

The example has the desired information in uppercase for consistency. The dictionary table will have the Library and dataset name in upper case as well. Variable names seem to be a bit more flexible as to case so I force an uppercase comparison.

 

Data control;
   informat libname $8. setname $32. varname $32.;
   input libname setname varname;
datalines;
SASHELP CLASS AGE
SASHELP CLASS WEIGHT
SASHELP CLASS XYZZY
;
RUN; 

PROC SQL;
   create table foundit as
   select control.libname, control.setname, control.varname,
          (control.varname=upcase(b.name)) as found
   from control left join dictionary.columns as b on
        control.libname=b.libname and control.setname=b.memname
        and control.varname=upcase(b.name)
   ;
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 340 views
  • 0 likes
  • 4 in conversation