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

Check for non-existing data element in a file

IsoTropic — Mon, 12 Sep 2016 20:52:17 GMT

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?

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

Reeza — Mon, 12 Sep 2016 21:21:30 GMT

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.

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

RW9 — Tue, 13 Sep 2016 12:32:55 GMT

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.

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

ballardw — Tue, 13 Sep 2016 15:15:33 GMT

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;

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

IsoTropic — Tue, 13 Sep 2016 16:20:15 GMT

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.