As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Difference between revisions of "Making Lists"

From sasCommunity
Jump to: navigation, search
(polishing, added ml-namex make-list-names-cr)
(2016-10-10 replaced section name and updated program)
Line 291: Line 291:
 
For usage see [[Processing_Variables]].
 
For usage see [[Processing_Variables]].
  
=== Proc Freq.nlevels, SCL functions, Cardinality Ratio (CR) and CR-type ===
+
=== MWSUG.2016.TT03: ml-namex.sas: Make List Names eXtended ===
 +
 
 +
previous:
 +
Proc Freq.nlevels, SCL functions, Cardinality Ratio (CR) and CR-type
 +
 
 +
[http://www.mwsug.org/proceedings/2016/TT/MWSUG-2016-TT03.pdf MWSUG-2016, Tools of the Trade, paper TT03, Calculating Cardinality Ratio in Two Steps]
  
 
Make List-Names with Cardinality Ratio and CR-type
 
Make List-Names with Cardinality Ratio and CR-type
Line 303: Line 308:
 
<source lang="sas">
 
<source lang="sas">
 
%put trace: ml-namex make-list-names-cr beginning;
 
%put trace: ml-namex make-list-names-cr beginning;
 +
%put echo parameters: &=libname &=memname;
 
  /*    name: ...\SAS-site\includes\ml-namex.sas
 
  /*    name: ...\SAS-site\includes\ml-namex.sas
     author: Ronald J. Fehd 2015
+
     author: Ronald J. Fehd 2016
----------------------------------------------------------
+
------------------------------------------------------
Summary: description: make list of data set variable names
+
Summary: description: make list of variable names,
                      with cardinality ratio and card-type
+
                      cardinality ratio and card-type
            purpose: provide for list processing routines
+
              purpose: for list processing routines
        -------------------------------------------------
+
          --------------------------------------------
 
Contexts: program group: procedure returns data set
 
Contexts: program group: procedure returns data set
 
           program  type: subroutine
 
           program  type: subroutine
           SAS      type: parameterized include procedure
+
           SAS      type: parameterized include
 
           uses routines: n/a
 
           uses routines: n/a
----------------------------------------------------------
+
------------------------------------------------------
 
Specifications: input  : macro variables
 
Specifications: input  : macro variables
 
                         libname: libref
 
                         libname: libref
                         memname: data set
+
                         memname: data set name
                 process: proc freq.n-levels
+
                 process: proc freq n-levels
 +
                        copy memname.nobs, .nvars
 +
                              from local to global
 
                         data: read var information
 
                         data: read var information
 
                               with scl functions
 
                               with scl functions
                              calculate cardinality ratio
+
                        calculate cardinality ratio
                                    and card-ratio-type
+
                              and card-ratio-type
 
                 output : list_names, a dimension table
 
                 output : list_names, a dimension table
 +
                        macro-variables: _length_few
 +
                        _list_few    _list_many_c
 +
                        _list_unique _list_many_n
 +
                        _max_length_c
 +
                          for data structure attrib
 +
                          valu_c length=$&_max_length_c
 
note: output is ordered by varnum
 
note: output is ordered by varnum
----------------------------------------------------------
+
--------------------------------------------------------
usage:
+
usage:   autoexec:
autoexec:
+
 
filename site_inc '<...\SAS-site\includes>';
 
filename site_inc '<...\SAS-site\includes>';
- - -  ...\SAS-site\sas-includes\put-global.sas - - -
 
%put _global_;
 
 
- - -  ml-names-x-test.sas - - -
 
- - -  ml-names-x-test.sas - - -
 
%let libname = sashelp;
 
%let libname = sashelp;
 
%let memname = class;
 
%let memname = class;
 
%include site_inc(ml-namex);
 
%include site_inc(ml-namex);
proc sql; describe table list_names; quit;
+
proc sql; describe table list_names;
 +
          quit;
 
proc print data = list_names;
 
proc print data = list_names;
%let cx_data    = work.list_names;
 
%let cx_include = site_inc(put_global)/source2;
 
%include site_inc(cx-inclu)/nosource2;
 
 
---------------------------------------------------------
 
---------------------------------------------------------
 
this is a Derivative Work of the SmryEachVar suite
 
this is a Derivative Work of the SmryEachVar suite
Line 345: Line 354:
 
this program is available on:
 
this program is available on:
 
http://www.sascommunity.org/wiki/Making_Lists
 
http://www.sascommunity.org/wiki/Making_Lists
---------------------------------------------------------
+
http://www.mwsug.org/2016-proceedings.html paper TT03
date-time: 8/26/2015 9:50:17 AM
+
it is designed to work with parameterized includes:
word count        words:  514
+
CxInclude, and SmryHuge; macros: CallMacro and CallText
                   lines:  138
+
-------------------------------------------------------
characters(no  spaces): 3609
+
date-time: 2016-08-24 4:00:50 PM
characters(with spaces): 5197
+
word count        words:  734
 +
                   lines:  195
 +
characters(no  spaces): 5410
 +
characters(with spaces): 7893
 
**** ..................... */
 
**** ..................... */
 +
**** make dimension table: name + n-levels;
 
ODS  exclude all; * noprint;
 
ODS  exclude all; * noprint;
**** make dimension table: name + n-levels;
+
PROC freq data   = &libname..&memname nlevels;
PROC freq data   = &libname..&memname
+
    ods output
          nlevels ;
+
           nlevels= list_names
          ods       output
+
         (keep   = tablevar           nlevels
           nlevels = out_n_levels
+
           rename =(tablevar= name     nlevels=n_levels));
         (keep   = tablevar nlevels
+
           rename = (tablevar = name
+
                    nlevels = n_levels));
+
 
run;
 
run;
 
ODS select all;
 
ODS select all;
%let _sys_last = &syslast;
 
  
****  make     mvars of n-obs and n-vars;
+
****  copy     n-obs, n-vars from local to global;
 
%let _dsid  = %sysfunc(open (&libname..&memname,i));
 
%let _dsid  = %sysfunc(open (&libname..&memname,i));
 
%let _n_obs  = %sysfunc(attrn(&_dsid,nobs));
 
%let _n_obs  = %sysfunc(attrn(&_dsid,nobs));
 +
%let _n_vars = %sysfunc(attrn(&_dsid,nvar));
 
%let _rc    = %sysfunc(close(&_dsid));
 
%let _rc    = %sysfunc(close(&_dsid));
%let _dsid  = %sysfunc(open (&syslast,i));
 
%let _n_vars = %sysfunc(attrn(&_dsid,nobs));
 
%let _rc    = %sysfunc(close(&_dsid));
 
%put info: &=_n_obs &=_n_vars;
 
 
%symdel _dsid _rc;
 
%symdel _dsid _rc;
  
 
**** enhance dimension table with variable information,
 
**** enhance dimension table with variable information,
     cardinality ratio (cr), card.ratio-type;
+
     cardinality ratio, card-ratio-type: cr-type;
DATA list_names(label=
+
DATA &syslast(label=
"list names &libname &memname nobs=&_n_obs vars=&_n_vars");
+
    "memname=&memname,obs=&_n_obs,vars=&_n_vars");
     attrib memname   length = $32
+
     attrib memname     length = $32
             varnum     length =  8 label = 'var num'
+
        %* primary key;
            name      length = $32 label = 'name'
+
             varnum     length =  8 label = 'var num'
             type      length = $ 1
+
        %* cardinality information;
            length     length =  8
+
             cr_type     length = $  %length(n-levels=1)
            cr_type    length = $  %length(n-levels=1)
+
                        label  =    'card. ratio type'
                      label  =    'card. ratio type'
+
             card_ratio length =  8     %*range=(0:1];
             card_ratio length =  8   %*range=(0:1];
+
                        format =    bestd7.5
                      format =    bestd7.5
+
                        label  =    'card. ratio'
                      label  =    'card. ratio'
+
             n_levels   length =  8
             n_levels   length =  8
+
                        label  =   "n-levels nobs=&_n_obs"
                      label  = "n-levels nobs=&_n_obs"
+
        %* variable information;
             format     length = $48
+
            name        length = $32 label = 'name'
           informat     length = $48
+
            type        length = $ 1 %*range:(c,n) from scl;
             label     length = $256;
+
            length      length =  8
 +
             format     length = $49
 +
           informat     length = $49
 +
             label       length =$256
 +
          _length_few  length =  4
 +
    %*** _temp vars for macro variables of lists;
 +
    %*** 33: length(var-name)=32 +1 for delimiter;
 +
    %let _max_length_list = %sysfunc(min(
 +
                        %eval(33*&_n_vars),32767));
 +
          _list_few    length = $&_max_length_list
 +
          _list_many_c length = $&_max_length_list
 +
          _list_many_n length = $&_max_length_list
 +
          _list_unique length = $&_max_length_list;
 +
          %put echo calculation: &=_max_length_list;
 +
    %symdel _max_length_list;
 
     array  _cr(&_n_vars);
 
     array  _cr(&_n_vars);
     drop  _:;       * _temporary variables;
+
    *****  _lfs: list-few-sorted value=000:name;
     retain memname     "&memname"
+
    array  _lfs(&_n_vars) $%eval(%length(&_n_obs)+33);
             _max_length 0;
+
     drop  _:;           * _temporary variables;
 +
     retain memname       "&memname"
 +
             _length_few 0 _max_length 1
 +
            _testing      %eval(
 +
            %sysfunc(getoption(mprint )) eq MPRINT
 +
        and %sysfunc(getoption(source2)) eq SOURCE2);
  
** read syslast, load array with card.ratio;
+
** loop: for each row, calculate cardinality ratio;
 
do _i = 1 to dim(_cr);
 
do _i = 1 to dim(_cr);
   set &syslast (keep = n_levels) point = _i;
+
   set &syslast   (keep = n_levels)   point = _i;
   _cr(_i) = n_levels/&_n_obs;
+
   _cr(_i) = n_levels / &_n_obs;
 
   end;
 
   end;
  
***** calculate mean for card.ratio-type;
+
***** calculate mean for select card_ratio to cr_type;
 
_mean_cr = mean(of _cr(*));
 
_mean_cr = mean(of _cr(*));
  
_dsid = open("&libname..&memname");
+
*****  loop: read syslast(name n_levels), fetch var info;
** read syslast, fetch info;
+
_dsid = open("&libname..&memname");** for scl functions;
 
do _i = 1 to dim(_cr);
 
do _i = 1 to dim(_cr);
   set &syslast   point = _i;
+
   set &syslast   point = _i; *** primary-key=name;
 +
  if _testing then putlog name= n_levels=;
 +
  varnum    =        varnum  (_dsid,name  );
 +
  type      = lowcase(vartype  (_dsid,varnum));
 +
  length    =        varlength(_dsid,varnum);
 +
  format    =        varfmt  (_dsid,varnum);
 +
  informat  =        varinfmt (_dsid,varnum);
 +
  label      =        varlabel (_dsid,varnum);
 
   card_ratio = _cr(_i);
 
   card_ratio = _cr(_i);
  varnum    = varnum  (_dsid,name);
 
  type      = lowcase  (
 
                vartype  (_dsid,varnum));
 
  length    = varlength(_dsid,varnum);
 
  format    = varfmt  (_dsid,varnum);
 
  informat  = varinfmt (_dsid,varnum);
 
  label      = varlabel (_dsid,varnum);
 
  ** for later use: max length of vartype=c;
 
  if type eq 'c' then
 
      _max_length = max(_max_length,length);
 
 
   select;
 
   select;
      when(n_levels  eq 1) cr_type = 'n-levels=1';
+
    when(n_levels  eq 1) cr_type = 'n-levels=1';
      when(card_ratio eq 1) cr_type = '.unique';
+
    when(card_ratio eq 1) cr_type = '.unique';%*row-id;
      when(card_ratio gt
+
    when(card_ratio gt
                  _mean_cr) cr_type = 'many';
+
                _mean_cr) cr_type = 'many'; %*analysis;
      otherwise            cr_type = 'few';
+
    otherwise            cr_type = 'few';  %*by|class;
 +
    end;
 +
  ** make list_many_c, _many_n, for mvars;
 +
  if      cr_type eq 'many' then do;
 +
      if      type eq 'c' then
 +
            _list_many_c = catx(' ',_list_many_c,name);
 +
      else if type eq 'n' then
 +
            _list_many_n = catx(' ',_list_many_n,name);
 
       end;
 
       end;
 +
  else if cr_type eq 'few'  then do;
 +
      **** save for sort: lfs(i)= '000:name';
 +
      _lfs(_i) = catx(':',put(n_levels
 +
                            ,z%length(&_n_obs).),name);
 +
      *** add one for delimiter = space;
 +
      _length_few = sum(1,_length_few);
 +
      if type eq 'c' then do;
 +
          _max_length=max(_max_length,length);
 +
          _length_few=sum(_length_few,length);
 +
          end;
 +
      else _length_few=sum(_length_few,%length(&_n_obs));
 +
      end;
 +
  else if cr_type eq '.unique'  then
 +
      _list_unique = catx(' ',_list_unique,name);
 
   output;
 
   output;
 
   end;
 
   end;
 
_rc = close(_dsid);
 
_rc = close(_dsid);
call symputx('_max_length_c',_max_length);
+
 
 +
**** loop: make list-few ordered by n-levels;
 +
call sortc(of _lfs(*));
 +
do _i = 1 to dim(_lfs);
 +
  if _testing and _lfs(_i) ne ' ' then putlog _lfs(_i)=;
 +
  ****        _lfs(_i)=0000:name;
 +
  name = scan(_lfs(_i),-1,':');
 +
  _list_few = catx(' ',_list_few,name);
 +
  end;
 +
 
 +
call symputx('_length_few'  ,_length_few );
 +
call symputx('_list_few'    ,_list_few  );
 +
call symputx('_list_many_c' ,_list_many_c);
 +
call symputx('_list_many_n' ,_list_many_n);
 +
call symputx('_list_unique' ,_list_unique);
 +
call symputx('_max_length_c',_max_length );
 
stop;
 
stop;
PROC delete data =    &_sys_last;
 
%symdel _n_obs _n_vars _sys_last;
 
 
run;
 
run;
 +
%put info: &=memname &=_n_obs &=_n_vars;
 +
%put info: &=_length_few;
 +
%put info: &=_list_few;
 +
%put info: &=_list_many_c;
 +
%put info: &=_list_many_n;
 +
%put info: &=_list_unique;
 
%put info: &=_max_length_c;
 
%put info: &=_max_length_c;
 
%put trace: ml-namex make-list-names-cr ending;
 
%put trace: ml-namex make-list-names-cr ending;

Revision as of 20:32, 10 October 2016

Making Lists for List Processing

Author: Ronald_J._Fehd

keywords: control data set

autoexec.sas

These examples use a fileref site_inc which is allocated in an autoexec.

filename site_inc '.';
*or;
*filename site_inc 'C:\SAS-site\includes';

Review SQL Dictionaries

PROC SQL;describe table dictionary.dictionaries;
         create table list as
         select distinct memname
         from dictionary.dictionaries;
 
         select catt('describe table dictionary.', memname)
		       into :list separated by ';'
		       from list;
       	 &list;
         quit;


Make List of Files

* name: ml-files-caller;
%let mlfolder = c:\temp;
%let mlout    = work.filenames;
%include site_inc(ml-files);
proc print data = &syslast;
* name       : ml-files;
* description: read folder, return list of filenames;
* purpose    : list processing;
* parameter  : ;
%*let mlfolder = c:\programs\sas-9-2\sasfoundation\9.2\stat\sample;
%*let mlout    = work.filenames;
%*include site_inc(ml-files);
 
data &mlout.(keep = filename);
     attrib filename length = $72;
rc        = filename("mydir","&mlfolder.");
did       = dopen   ("mydir");
dim_files = dnum(did);
do i = 1 to dim_files;
   filename = dread(did,i);
   output;
   end;
rc = dclose(did);
run;


see also

contributed by Nat Wooding to SAS-L on 2010-11-04;

Subject: RE: Can SAS pull info about MS Window folders?;

** this uses a pipe to gather the names of files in a folder
and reads a single line from each file.
note the quotes in the filename statement;
 
filename ft33f001 pipe  'attrib "c:\*.txt" '  ;
 
data _null_;
   infile ft33f001 truncover;
   input @12 csvfile $256.;
   infile dummy filevar=csvfile firstobs=122 length=l   ;
 
   input line $varying256. l   ;
   putlog 'NOTE: ' line=;
run;
 
*the following is an attempt at reading directory info;
FileName MyDir Pipe  'dir "C:\park"  ' ;
 
DATA all_dir;
infile mydir lrecl=300 truncover ;
input  @ ;
file print;
 
if index( _infile_ , '/' ) ;* get lines with dates;
if index( _infile_ , '<' ) then delete;* get rid of directories;
 
informat date mmddyy10. time time. ampm $2.   Size comma15. Name $25.;
 ;
input date  time  ampm    Size  Name &  ;
 
run;
proc print;
run;

Make List of Folders

* name: ml-folders-caller;
%let mlfolder = c:\;
%let mlout    = work.folders;
%include site_inc(ml-folders);
proc print data = &syslast;

Note: Windows folder do not have a dot after FileName.

* name       : MlFolders;
* description: make list of folders;
* purpose    : list processing;
* parameters ;
%*Let MlFolder = C:\Program Files\;
%*Let MlOut    = work.folders;
%include site_inc(ml-files);
 
DATA &mlout(keep = folder);
     attrib folder length = $132;
do until(endofile);
   set &mlout (where = (not index(filename,'.')))
       end = endofile;
   folder = catt("&mlfolder.\",filename);
   if fileexist(folder) then output;
   end;
stop;
run;


filename pipe

Date:    Tue, 12 May 2015 17:28:51 -0400
To:      SAS-L
From:    Roger DeAngelis
Subject: Re: List of subfolders
filename pipetree pipe 'tree "C:\AYE835" /F /A' lrecl=3D5000;
data _null_;
infile pipetree truncover;
input dirlist $char1000.;
if index(dirlist,'.') =0;
put dirlist;
run;

Make List of Libnames

PROC SQL; describe table dictionary.libnames;
          create table work.listlibnames as
          select *
          from dictionary.libnames;
          quit;
* name: ml-libname-caller;
%let mllprefix = sashelp;
%include site_inc(ml-libnames);
PROC print data = &syslast;
* name: ml-libnames;
PROC sql;
     create table work.listlibnames as
     select *
     from dictionary.libnames
     where libname like "%upcase(&mllprefix.%)"
   ;
quit;

Make List of Memnames

Proc Contents

The output of Proc Contents is always at a detail level of a library member. The details are things like attributes of the variables or the properties of the indexes.

Proc Datasets

%let libref = work;
%let out = work.library_members;
 
PROC sql; drop table &out;
          quit;
 
ods listing select none;
ods output members = &out
          (label   = "members of &libref library");
PROC datasets library = &libref;
run;
quit;
ods output close;
ods listing select all;
 
PROC print data=library_members;
run;


Proc SQL

* name: ml-memnames-caller;
%let mlmlibname = library;
%let mlmlibname = sashelp;
%include site_inc(ml-memnames);
 
proc print data = &syslast;
* name: ml-memnames;
PROC sql; create table work.listmemnames as
          select *
          from   dictionary.tables
          where  libname eq "%upcase(&mlmlibname.)"
            and  memtype eq 'DATA'
          /* order by name | varnum /* ** */
          ;
          quit;

Make List of Names: Columns or Variables

Proc Contents

* name: ml-names-caller;
%let mlnlibname = library;
%let mlnlibname = sashelp;
%let mlnmemname = _all_;
%let mlnmemname = class;
%include site_inc(ml-names);
Proc print data = &syslast;
* name: ml-names;
PROC contents data   = &mlnlibname..&mlnmemnames.
                       noprint
              out    = work.listnames
             (keep   = libname memname name type length label varnum
              where  = (memtype eq 'DATA') );
 
PROC sort data = &syslast;
          *by     name  ;* alphabetical;
          *by     varnum;* order in data set;

Proc SQL

* name: ml-names-caller;
%let mlnlibname = library;
%let mlnlibname = sashelp;
%let mlnmemname = class;
%include site_inc(ml-names);
proc print data = &syslast;
* name: ml-names;
PROC sql; create table work.listnames as
          select *
          from   dictionary.columns
          where  libname eq "%upcase(&mlnlibname.)"
            and  memname eq "%upcase(&mlnmemname.)"
            and  memtype eq 'data';
          quit;

For usage see Processing_Variables.

MWSUG.2016.TT03: ml-namex.sas: Make List Names eXtended

previous: Proc Freq.nlevels, SCL functions, Cardinality Ratio (CR) and CR-type

MWSUG-2016, Tools of the Trade, paper TT03, Calculating Cardinality Ratio in Two Steps

Make List-Names with Cardinality Ratio and CR-type see also Cardinality_Ratio

Note Style Guide enhancements:

  •  %put
    • trace: <program-name> beginning and exit
    • info: _temporary macro variables created
%put trace: ml-namex make-list-names-cr beginning;
%put echo parameters: &=libname &=memname;
 /*    name: ...\SAS-site\includes\ml-namex.sas
     author: Ronald J. Fehd 2016
------------------------------------------------------
Summary:  description: make list of variable names,
                       cardinality ratio and card-type
              purpose: for list processing routines
          --------------------------------------------
Contexts: program group: procedure returns data set
          program  type: subroutine
          SAS      type: parameterized include
          uses routines: n/a
------------------------------------------------------
Specifications: input  : macro variables
                         libname: libref
                         memname: data set name
                process: proc freq n-levels
                         copy memname.nobs, .nvars
                               from local to global
                         data: read var information
                               with scl functions
                         calculate cardinality ratio
                               and card-ratio-type
                output : list_names, a dimension table
                         macro-variables: _length_few
                         _list_few    _list_many_c
                         _list_unique _list_many_n
                         _max_length_c
                           for data structure attrib
                           valu_c length=$&_max_length_c
note: output is ordered by varnum
--------------------------------------------------------
usage:   autoexec:
filename site_inc '<...\SAS-site\includes>';
- - -  ml-names-x-test.sas - - -
%let libname = sashelp;
%let memname = class;
%include site_inc(ml-namex);
proc sql; describe table list_names;
          quit;
proc print data = list_names;
---------------------------------------------------------
this is a Derivative Work of the SmryEachVar suite
www.sascommunity.org/wiki/SmryEachVar_A_Data_Review_Suite
this program is available on:
http://www.sascommunity.org/wiki/Making_Lists
http://www.mwsug.org/2016-proceedings.html paper TT03
it is designed to work with parameterized includes:
CxInclude, and SmryHuge; macros: CallMacro and CallText
-------------------------------------------------------
date-time: 2016-08-24 4:00:50 PM
word count        words:  734
                  lines:  195
characters(no   spaces): 5410
characters(with spaces): 7893
**** ..................... */
**** make dimension table: name + n-levels;
ODS  exclude all; * noprint;
PROC freq data   = &libname..&memname nlevels;
     ods  output
          nlevels= list_names
         (keep   = tablevar           nlevels
          rename =(tablevar= name     nlevels=n_levels));
run;
ODS select all;
 
****  copy      n-obs, n-vars from local to global;
%let _dsid   = %sysfunc(open (&libname..&memname,i));
%let _n_obs  = %sysfunc(attrn(&_dsid,nobs));
%let _n_vars = %sysfunc(attrn(&_dsid,nvar));
%let _rc     = %sysfunc(close(&_dsid));
%symdel _dsid _rc;
 
**** enhance dimension table with variable information,
     cardinality ratio, card-ratio-type: cr-type;
DATA &syslast(label=
    "memname=&memname,obs=&_n_obs,vars=&_n_vars");
     attrib memname     length = $32
         %* primary key;
            varnum      length =   8 label = 'var num'
         %* cardinality information;
            cr_type     length = $  %length(n-levels=1)
                        label  =    'card. ratio type'
            card_ratio  length =   8     %*range=(0:1];
                        format =    bestd7.5
                        label  =    'card. ratio'
            n_levels    length =   8
                        label  =    "n-levels nobs=&_n_obs"
         %* variable information;
            name        length = $32 label = 'name'
            type        length = $ 1 %*range:(c,n) from scl;
            length      length =   8
            format      length = $49
          informat      length = $49
            label       length =$256
           _length_few  length =   4
     %*** _temp vars for macro variables of lists;
     %*** 33: length(var-name)=32 +1 for delimiter;
     %let _max_length_list = %sysfunc(min(
                        %eval(33*&_n_vars),32767));
           _list_few    length = $&_max_length_list
           _list_many_c length = $&_max_length_list
           _list_many_n length = $&_max_length_list
           _list_unique length = $&_max_length_list;
          %put echo calculation: &=_max_length_list;
     %symdel _max_length_list;
     array  _cr(&_n_vars);
     *****  _lfs: list-few-sorted value=000:name;
     array  _lfs(&_n_vars) $%eval(%length(&_n_obs)+33);
     drop   _:;            * _temporary variables;
     retain memname        "&memname"
            _length_few 0  _max_length 1
            _testing       %eval(
             %sysfunc(getoption(mprint )) eq MPRINT
         and %sysfunc(getoption(source2)) eq SOURCE2);
 
** loop: for each row, calculate cardinality ratio;
do _i = 1 to dim(_cr);
   set &syslast   (keep = n_levels)   point = _i;
   _cr(_i) = n_levels / &_n_obs;
   end;
 
***** calculate mean for select card_ratio to cr_type;
_mean_cr = mean(of _cr(*));
 
*****   loop: read syslast(name n_levels), fetch var info;
_dsid = open("&libname..&memname");** for scl functions;
do _i = 1 to dim(_cr);
   set &syslast   point = _i; *** primary-key=name;
   if _testing then putlog name= n_levels=;
   varnum     =         varnum   (_dsid,name  );
   type       = lowcase(vartype  (_dsid,varnum));
   length     =         varlength(_dsid,varnum);
   format     =         varfmt   (_dsid,varnum);
   informat   =         varinfmt (_dsid,varnum);
   label      =         varlabel (_dsid,varnum);
   card_ratio = _cr(_i);
   select;
     when(n_levels   eq 1) cr_type = 'n-levels=1';
     when(card_ratio eq 1) cr_type = '.unique';%*row-id;
     when(card_ratio gt
                 _mean_cr) cr_type = 'many'; %*analysis;
     otherwise             cr_type = 'few';  %*by|class;
     end;
   ** make list_many_c, _many_n, for mvars;
   if      cr_type eq 'many' then do;
      if      type eq 'c' then
            _list_many_c = catx(' ',_list_many_c,name);
      else if type eq 'n' then
            _list_many_n = catx(' ',_list_many_n,name);
      end;
   else if cr_type eq 'few'  then do;
      **** save for sort: lfs(i)= '000:name';
      _lfs(_i) = catx(':',put(n_levels
                             ,z%length(&_n_obs).),name);
      *** add one for delimiter = space;
      _length_few = sum(1,_length_few);
      if type eq 'c' then do;
           _max_length=max(_max_length,length);
           _length_few=sum(_length_few,length);
           end;
      else _length_few=sum(_length_few,%length(&_n_obs));
      end;
   else if cr_type eq '.unique'  then
       _list_unique = catx(' ',_list_unique,name);
   output;
   end;
_rc = close(_dsid);
 
**** loop: make list-few ordered by n-levels;
call sortc(of _lfs(*));
do _i = 1 to dim(_lfs);
   if _testing and _lfs(_i) ne ' ' then putlog _lfs(_i)=;
   ****        _lfs(_i)=0000:name;
   name = scan(_lfs(_i),-1,':');
   _list_few = catx(' ',_list_few,name);
   end;
 
call symputx('_length_few'  ,_length_few );
call symputx('_list_few'    ,_list_few   );
call symputx('_list_many_c' ,_list_many_c);
call symputx('_list_many_n' ,_list_many_n);
call symputx('_list_unique' ,_list_unique);
call symputx('_max_length_c',_max_length );
stop;
run;
%put info: &=memname &=_n_obs &=_n_vars;
%put info: &=_length_few;
%put info: &=_list_few;
%put info: &=_list_many_c;
%put info: &=_list_many_n;
%put info: &=_list_unique;
%put info: &=_max_length_c;
%put trace: ml-namex make-list-names-cr ending;

Auto Import all Excel Worksheets

The SAS Tech Report has a link to a usage note that lists new SAS tools that speed up the process of importing Excel workbooks or Access data bases. The title of the note is

Usage Note 13348: Using SAS(r) autoimporterv9 programs to automatically import all worksheets in a Microsoft Excel workbook And the url is

http://support.sas.com/kb/13/348.html

Make List of Excel SheetNames

Little Birdie opines:

I think all but one of the solutions put forth so far require SAS/ACCESS for PC Files, and the remaining one requires that SAS is running on Windows. If you don't meet these requirements, then you cannot use the techniques.

I just thought that I might save you some time, not having to try solutions that are not applicable to your environment.

http://listserv.uga.edu/cgi-bin/wa?A2=ind0107C&L=sas-l&P=R1794&D=0&H=0&O=D&T=1

  • Date: Mon, 16 Jul 2001 15:15:11 +0100
  • Reply-To: "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
  • Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
  • Subject: How to get existing Excel worksheet names into SAS

Untested

filename topics dde 'excel|system!topics' lrecl=5000;
 
data _null_;
length topics $1000;
infile topics pad dsd notab dlm='09'x;
input topics $ @@;
put topics;
run;

provided by Ed Heaton

libname xlslib "&workbook" access=readonly ;
proc sql; create table _data_ as
          select memname
          from dictionary.tables
          where (    ( libname eq "XLSLIB" )
                  /* in case you don't want named ranges,
                   code the following. */
                 and (memname contains '$' )
                 and (scan( dequote(memname) , 2 , '$' ) is missing )
                 );
          quit;
%let worksheets = &syslast ;
libname xlslib clear ;

provided by John Gerstle

libname test excel "&datapath.\Test..xls"
             ver    = 2002
             header = yes
             mixed  = yes;
 
proc sql;select count(distinct memname), memname
	        into   :no_census_datasets
            ,   :census_datasets separated by ' '
	        from   dictionary.members
	        where  memtype='DATA'
           and  libname="TEST";
quit;
run;
%put &no_census_datasets;
%put &census_datasets;
 
libname test close;
* name: ml-sheets;
%*let folder =;
%*let filename =;
%*let mlsout = ;
data   work.sheetnames;
attrib folder    length = $132
       filename  length = $ 72
       sheetname length = $132;
** automagic happens here;
* do ...;
*    output;
*    end;
* stop;
 
proc append base = &mlsout.
            data = work.sheetnames;
run;
* name: ml-sheets-caller;
%let mlffolder = ..\xls;
%let mlfout     = work.listxls;
%include site_inc(ml-files);
 
%let mlxfolder = ..\xls;
%let mlxout     = work.listsheets;
%include site_inc(ml-sheets);
 
%let cx_data    = &mlxout;
%let cx_include = site_inc(mlsheets);* returns work.listsheets
%include site_inc(ml-files);
 
%let cx_data    = &mlfout;
%let cx_include = site_inc(importxls);
%include site_inc(ml-files);
 
%let mlslibname = sashelp;
%let mlsmemname = class;
%let mlsname    = sex;
%let mlsout     = work.listsheets;
%include site_inc(ml-sheets);
proc print data = &syslast;


Make List of Subsets

Proc Freq

* name: mls-freq-caller;
%let mlslibname = library;
%let mlslibname = sashelp;
%let mlsmemname = class;
%let mlsname    = sex;
%let mlsout     = work.listsexes;
%include site_inc(mls-freq);
proc print data = &syslast;
* name: MlsFreq.sas;
Proc Freq data   = &MlsLibname..&MlsMemname;
          tables   &MlsName
                 / list missing noprint
             out = &MlsOut;
run;

Proc Index

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1210A&L=sas-l&P=R3656

As of the 2012-Oct-02 this is a spoof. Check the SASware.Ballot to see if it has been implemented.

PROC Index data  = library.Mydata
           index = MyData_VarA
           out   = Work.List_MyData_VarA;


Proc Sort

* name: mls-sort-caller;
%let mlslibname = library;
%let mlslibname = sashelp;
%let mlsmemname = class;
%let mlsnames   = sex;
%let mlsout     = work.listsexes;
%include site_inc(mls-sort);
proc print data = &syslast;
* name: mls-sort.sas;
proc sort data = &mlslibname..&mlsmemname.
         (keep = &mlsnames)
          out  = &mlsout
                 nodupkey;
          by     &mlsnames;
run;

Proc SQL

* name:;
select distinct ...;

Proc Summary

* name:;

Proc Univariate

* name:;

References

-- created by User:Rjf2 18 August 2008

--Ronald_J._Fehd macro.maven == the radical programmer 07:37, 24 June 2012 (EDT)