How to check a group of datasets with a group of variables with a certain value

[Bal23]

I want to check how many obs with patient ID that has value as "-1". The variables could be patientid, id, or sampleid. They are charcter variables, when it =-1, it has a different meaning.

I have 30 datasets to check. In stead of opening one by one and have the command, I would get advice from you whether I can have a macro to check,

proc select where patientid or id or sampleid in "-1"

any suggestions?

Thanks

[art297]

Do all of the files contain all three id variables? Do you have a file that contains a lists of the 30 file names (including directories) or, if not, are they all in the same directory?

Regardless, since you want counts, I'd create a format that recodes the -1 to, say 1, and anything else to zero, then use proc freq. That way you could easily get both counts and percentages.

Art, CEO, AnalystFinder.com

[KevinViel]

Wenling,

  You need a macro, unless you like to type.  SASHELP is your friend.  I assume that -1 is an example.  I did not assume that the variable would only be char.  The following is untested:

%macro id_minus_one

  ( path =

  , vars = patientid

           id

           sampleid

  , value = -1

  ) ;

  %if %nrbquote(&path.) = %str()

  %then

     %do ;

         %put WAR%str(NING): path is a required parameter. ;

         %goto __END ;

     %end ;

  %if %sysfunc( fileexist( &path. )) = 0

  %then

     %do ;

         %put WAR%str(NING): Path does not exist: &path. ;

         %goto __END ;

     %end ;

　

  libname lib

          "&path."

          access = readonly

          ;

  /**************************************************/

  proc sql noprint ;

    select memname

         , upcase( name )

         , type

             into : ds1 - : ds999

                , : var1 - : var999

                , : t1 - : t999

    from sashelp.vcolumn

    where     upcase( libname ) = "LIB"

          and upcase( name ) in

                 ( "%sysfunc( prxchange( s/\s+/%str(%" , %")/ , -1 , %upcase(&vars.)))" )

   ;

  quit ;

  %do __i = 1 %to &sqlobs. ;

     %let __j = 1 ;

     %let v = %scan( &vars. , &__j. , %str()) ;

     %do %while ( &v. ne ) ;

        proc sql ;

          create table var as

          select distinct "&&&ds&__i." as Dataset

                    length = 32

               , "&&&var&__i." as Variable

                    length = 32

               , "&&&t&__i." as Type

          from lib.&&&ds&__i.

          where &&&var&__i. = %if &&&t&__i. = char %then "&value." ;

                                %else &value. ;

          ;

        quit ;

        proc append

           base = id_minus_1

           data = var

           ;

        run ;

        proc datasets

           library = WORK

           NoList

           ;

          delete var ;

        quit ;

    %let __j = %eval( &__j. + 1 ) ;

    %let v = %scan( &vars. , &__j. , %str()) ;

   %end ;

  %end ; /* CYCLE THROUGH __i */

  %__END:

%mend id_minus_one ;

　

%id_minus_one

( path = C:\TEST ) ;

Good luck,

Kevin

PS Formatting is an issue on SAS Communities.  Check the code carefully, becuase parts may have been lost in the transfer/formatting.