topic Re: How to check all the variables in multiple datasets (single folder) in SAS Programming

How to check all the variables in multiple datasets (single folder)

2222 — Thu, 22 Dec 2022 05:04:02 GMT

Hello community,

Can anyone help me to see if in multiple datasets an array or list of variables exists? Ideally, the code would check the "dirty" datasets and then compare these to the corresponding "cleaned" dataset checking for a set of variables that have (hopefully) been removed from the "dirty" datasets making them the "cleaned" datasets. For example, datasets1-5 have variables that have been removed to create dataset1(-5)_cleaned and id like some code to check to make sure all the "cleaning" has been done by returning a 0 or 1 or exists/does not exist for each variable in each cleaned dataset. (using sas EG)
Hope this makes some sense.

Re: How to check all the variables in multiple datasets (single folder)

Kurt_Bremser — Thu, 22 Dec 2022 05:53:30 GMT

Create dataset with your "allowed" variable names.

Then, use DICTIONARY.COLUMNS:

proc sql;
create table unclean as
  select memname, name
  from dictionary.columns
  where libname = "YOURLIB" and upcase(name) not in (select upcase(name) from allowed)
;
quit;

libname must be in uppercase.

Re: How to check all the variables in multiple datasets (single folder)

2222 — Thu, 22 Dec 2022 07:14:35 GMT

hi Kurt, thank you for replying.

could you please tell me what that code you wrote does and where do i put all the variables to check?

Re: How to check all the variables in multiple datasets (single folder)

Kurt_Bremser — Thu, 22 Dec 2022 08:48:33 GMT

proc sql;
create table unclean as
  select memname, name /* these are variables in the COLUMNS pseudo-table */
  from dictionary.columns
  where
    libname = "YOURLIB" /* this should be the library containing your datasets */
    and upcase(name) not in
      (select upcase(name) from allowed) /* a sub-select querying a dataset */
;
quit;

The dataset WORK.ALLOWED in my example needs to contain your valid variable names.

Re: How to check all the variables in multiple datasets (single folder)

2222 — Fri, 23 Dec 2022 02:23:14 GMT

Hi Kurt,

Thanks so much for the explanation. I tried this but got an error i dont know how to fix (below):

proc sql;
create table unclean as
select memname, a90_cleaned, a95_cleaned /* these are variables in the COLUMNS pseudo-table */ (these are two of the many variables to check)
from dictionary.columns
where
libname = "\\SAS\D\S20\A\DATA" /* this should be the library containing your datasets */ (all the datasets are in this folder)
and upcase(name) not in
(select upcase(name) from allowed) /* a sub-select querying a dataset */
;
quit;

Re: How to check all the variables in multiple datasets (single folder)

Kurt_Bremser — Fri, 23 Dec 2022 06:07:04 GMT

You have to create the dataset WORK.ALLOWED first, with a variable called NAME which contains your valid variable names. You can use a DATA step with DATALINES for this (unless you already have another source for this).

Re: How to check all the variables in multiple datasets (single folder)

2222 — Tue, 03 Jan 2023 06:25:15 GMT

Hi Kurt,

Thank you and happy new year!

I think I have miscommunicated the goal and so please forgive me for making it more complicated that it needed to be.

What I'm trying to do is see if, within a dataset, a certain variable value has been removed. The variable itself (the column name) will remain but the goal of the checking is to make sure sure that the cell has been cleared. There are 10 datasets and about 10 variables in each ds (some of which have the same name in each ds). So i just want to make sure that the code that cleans the dirty dataset has done its job. Maybe a count and compare would do it?

Re: How to check all the variables in multiple datasets (single folder)

2222 — Tue, 03 Jan 2023 08:05:08 GMT

I did this and the results say 0 for each variable so im assuming that means there is nothing in each of the variable columns, but this seems wrong/ugly and if i had a million variables id have to type them all in which seems wrong:

libname mylib "\\folder path";

proc sql;
select count(a7) as a7,
count(e1) as e1,
count(m2) as m2,
count(p) as p,
from mylib.w90_cleaned;
quit;

Re: How to check all the variables in multiple datasets (single folder)

Kurt_Bremser — Tue, 03 Jan 2023 10:04:19 GMT

So you do not want to remove variables, but values??

Re: How to check all the variables in multiple datasets (single folder)

fja — Tue, 03 Jan 2023 10:19:09 GMT

I do not fully understand your problem.
1. Do you want to query a specific table/dataset or are you after a generic solution (i.e. applicable to any dataset)?
2. What does "variable has been cleared" mean? Would you like to check a table per row if a certain variable and its value?
3. ... and following #2: What does "cleared" mean. Set to something or containing "." (i.e. is missing)?
--fja

Re: How to check all the variables in multiple datasets (single folder)

2222 — Wed, 04 Jan 2023 01:17:15 GMT

Someone else has written code to remove the values (these are old values that are no longer valid), i want to check to make sure that the values have indeed been removed.

Re: How to check all the variables in multiple datasets (single folder)

2222 — Wed, 04 Jan 2023 01:23:38 GMT

hello fja, thank you. cleared means the values have been deleted/removed, the variable, as a column header still remains, but i need to make sure/check that the values for the variable are all gone/have been removed. some values are string, some are numbers. there are multiple datasets that i need to check

Re: How to check all the variables in multiple datasets (single folder)

Kurt_Bremser — Wed, 04 Jan 2023 01:24:03 GMT

So you need to check for the given variables that the count of non-missing values is zero?

Re: How to check all the variables in multiple datasets (single folder)

2222 — Wed, 04 Jan 2023 01:27:34 GMT

yes sir. wish i had written it like you just did, sorry.

Re: How to check all the variables in multiple datasets (single folder)

Patrick — Wed, 04 Jan 2023 02:04:57 GMT

@2222 Before talking this any further can you please confirm if below code is heading into the direction of what you're after?

If it is not then can you please be as specific as possible what you want and ideally provide a have and a want table/example so we can understand.

If below code is heading into the direction of what you're after then please specify further:
1. Are the variables to test of type character or of type numeric?

2. Can there be more than one value you need to search for?

3. Is this a one-off or something you need to run on a regular basis?

...and I just read in your latest post that "some variables are string, some are numbers": I guess this means that unlike to your initial post you actually need to check for different values (or list of values) per variable. Can you please be specific and best provide some representative sample data and desired result as this often reduces requirement ambiguity a lot.

%macro exceptions(libname=,memname=,varname=,value=,exception_tbl=work.exceptions);

  proc datasets lib=work nolist nowarn;
    delete __exceptions;
  quit;

  data work.__exceptions;
    length
      __libname $8
      __memname $32
      __rownum  8
      __varname $32
      __value   $100
      ;
    keep
      __libname
      __memname
      __rownum
      __varname
      __value
      ;
    rename 
      __libname=libname
      __memname=memname
      __rownum =rownum
      __varname=varname
      __value  =value
      ;

    set &libname..&memname;
    if &varname="&value" then
      do;
        __libname="%upcase(&libname)";
        __memname="%upcase(&memname)";
        __rownum=_n_;
        __varname="&varname";
        __value="&value";
        output;
      end;
  run;

  proc append base=&exception_tbl data=work.__exceptions;
  run;

%mend;


proc datasets lib=work nolist nowarn;
  delete exceptions;
quit;

%exceptions(libname=sashelp,memname=class,varname=name,value=Louise);
%exceptions(libname=sashelp,memname=classfit,varname=name,value=Louise);

proc print data=work.exceptions;
run;

Re: How to check all the variables in multiple datasets (single folder)

2222 — Wed, 04 Jan 2023 02:08:29 GMT

Hello Patrick, thank you for your reply and help. It really is just as Kirk said "So you need to check for the given variables that the count of non-missing values is zero?"

Re: How to check all the variables in multiple datasets (single folder)

Patrick — Wed, 04 Jan 2023 03:49:39 GMT

@2222 wrote:
Hello Patrick, thank you for your reply and help. It really is just as Kirk said "So you need to check for the given variables that the count of non-missing values is zero?"

Then I leave it to @Kurt_Bremser to support you further.

The way I understood your requirement only certain values would have been deleted resulting in the variable having rows ("cells") with missing values and other rows with actual still valid values.

That's what the code I posted is doing: Creating an exception table for all the "cells" with invalid values (the ones that should have been deleted). If everything is o.k. then this exception table has no rows.

Re: How to check all the variables in multiple datasets (single folder)

Tom — Wed, 04 Jan 2023 04:37:37 GMT

@2222 wrote:
Someone else has written code to remove the values (these are old values that are no longer valid), i want to check to make sure that the values have indeed been removed.

What does that mean?

Terminology clarification. SAS datasets consist of OBSERVATIONS (what you might call a row in a spreadsheet or a printed reported) and VARIABLES (what you might call a column in a spreadsheet or a printed report).

Do you mean they changed the values of some the variables to something else? What did they change them to?
Do you mean they deleted the OBSERVATIONS that had an invalid value for at least one of the VARIABLES?

How do you know which values are invalid? What makes them invalid?

You mentioned before about comparing the "original" and the "clean" dataset. If you have a lot of datasets how to know which ones to compare to each other? Is there some pattern in the NAME of the datasets?

Re: How to check all the variables in multiple datasets (single folder)

2222 — Wed, 04 Jan 2023 04:51:33 GMT

Hi Tom, thanks for replying. Kurt is onto it i think. Basically there are datasets that have observations that were outdated (all were outdated) and someone deleted them all by hand. My job is to check, that for each specified variable within a dataset there should are no observations (make sure they have indeed all been removed). I made a mistake by saying i wanted to compare them and Kurt helped to clarify the wording of what we actually want to do, there is no need to compare the datasets, we just want to check the datasets have been "cleaned" ie had all the observations for the variables in question, removed. We still need to keep the variables though because the structure of the dataset needs to remain the same.

Re: How to check all the variables in multiple datasets (single folder)

Kurt_Bremser — Wed, 04 Jan 2023 10:21:21 GMT

Please use correct diction. A variable cannot "have observations".

A dataset consists of observations (records or rows), which contain variables (columns), and the variables contain values.

As long as there are observations in a dataset, all variables defined will have values. These values may be missing or non-missing. You cannot have a "variable with no observations", unless the dataset is empty (no observations at all).

In light of this, restate your issue, using the correct terms.