How to identify and delete empty datasets in an elegant manner

sas

    Sponsored Links

    Next

  • 1. The Deck of The Enterprise awash in Business Intelligence...
    Just came back from my first SUGI in Montreal. It was a positive experience where I learned alot. However.... I was really annoyed that the focus of the Opening Session was on Business Intelligence Across The Enterprise (will Captain's Kirk/Picard/Archer be appearing?). I probably represent one of the "old school" SAS customers -- non-profit medical outcomes research. I understand that this is not a growth area and that SAS pushing hard into other markets. However, when the terms "Enterprise" and "Business Intelligence" are liberally brandished, I think that they are speaking to big company management and IT folks, and not to me, lowly SAS programmer/analyst at a non-profit research group. Why was I there (besides the cool acrobatic show)? Did anyone else feel this way, or are my attitudes out of line? Craig Lake
  • 2. Clarification on 慴y' statement
    Hello all, I have a program below, dataset one is not sorted, when I run the program under sas ver8.2 on pc, no error massage come out. libname tx 'M:\ver8\data'; libname tin 'M:\ver8\data'; libname library 'M:\ver8\lib'; options fmtsearch=(library.txca); Data two; Set tx.one; By id; Run; But, if I run the program using sas ver612 datasets under sas ver8.2 on pc, the following error showed on log. RROR: BY variables are not properly sorted on data set tx.one' libname tx v612 'M:\ver6\data'; libname tin v612 'M:\ver6\data'; libname library 'M:\ver\lib'; options fmtsearch=(library.txca); Data two; Set tx.one; By id; Run; I understand that we need to do roc sort' before we can use y' statement in dataset. Why the error massage did not show. Any advice? Thank you in advance, Kevin
  • 3. selecting top twovalues
    Hi all, Was wondering if someone could help me out on this: I have the following data (just an example): Patient1 Patient2 Patient3 Hospital1 20 5 10 Hospital2 2 1 50 Hospital3 23 40 1 Basically what I'm trying to do is to have a DATASET that has the highest two Patients from each hospital: example. Looking for a dataset, not a report output. Dataset: Hospital High Highnumvalue SecondHigh Secondhighvalue Hospital1 Patient1 20 Patient3 10 Hospital2 Patient3 50 Patient1 2 Hospital3 Patient2 40 Patient1 23 Any help would be great.

How to identify and delete empty datasets in an elegant manner

Postby Shelley.Baxter » Sun, 26 Mar 2006 02:58:01 GMT

Would this be a proc sql approach with the vtable?

Shelley S. Baxter, Ph.D.
Boeing Corp.
            509-637-4500

Re: How to identify and delete empty datasets in an elegant manner

Postby Jiann-Shiun.Huang » Sun, 26 Mar 2006 03:47:32 GMT

Shelley:

  If your datasets are "ORDINARY DATASET" as mentioned in Jack's paper,
then the following code should work for you.  You need to change
LibraryName to your libname without any quotation.

%let MyLib=%upcase(LibraryName);  /*Without any quote*/
proc sql noprint;
  select memname into :NameList separated by ' '
  from sashelp.vtable
  where libname="&MyLib" and nobs eq 0;
quit;

%put &NameList;

%macro droptbl(lib,nooblist);
  %let filecnt=%eval(%sysfunc(count(&nooblist,%str( )))+1);
  %put &filecnt;
  proc sql;
    %do i=1 %to &filecnt;
          %let dataset=%scan(&nooblist,&i);
          drop table &lib..&dataset;
    %end;
  quit;
%mend droptbl;

%droptbl(&MyLib,&NameList)
run;


J S Huang
1-515-557-3987
fax 1-515-557-2422

That's would I would do, if I knew the provenance of the data sets.
I'd probably use dictionary.tables rather than sashelp.vtable - it's
supposedly more efficient.

You do have to know how your data sets were created and modified -
there are some circumstances under which the observation count in
dictionary.tables will not be correct.  See my paper How Many
Observations Are In My Dataset?" at

< http://www.**--****.com/ ;.








---
Jack Hamilton
Sacramento, California

Re: How to identify and delete empty datasets in an elegant manner

Postby joewhitehurst » Sun, 26 Mar 2006 04:45:24 GMT

Or, you might consider the folllowing simple SAS Component Language program.

length memname $32;
init:
  submit continue;
     proc contents data=newtable._all_ dir noprint out=lib_contents;
     run;
  endsubmit;
lib_contents_dsid=open('lib_contents','i');
do until(fetch(lib_contents_dsid)^=0);
   temp_dsid=open('sashelp.'||memname,'v');
   if attrn(temp_dsid,'nobs')=0 then do;
      close_rc=close(temp_dsid);
      delete_rc=delete('newtable.'||memname);
  end;
end;
close_rc=close(lib_contents_dsid);
return;



Re: How to identify and delete empty datasets in an elegant manner

Postby shiling99 » Sun, 26 Mar 2006 04:48:52 GMT

You may modify the SQL below to achieve your needs.
HTH

1    data;
2    stop;
3    run;

NOTE: The data set WORK.DATA1 has 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.14 seconds
      cpu time            0.00 seconds


4    data;
5    stop;
6    run;

NOTE: The data set WORK.DATA2 has 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds


7    data;
8    stop;
9    run;

NOTE: The data set WORK.DATA3 has 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


10
11   proc sql noprint;
12     select 'drop table '||compress(libname)||'.'||memname into:
dropstmt separated by ';'
13     from sashelp.vtable
14     where nobs=0 and memtype='DATA' and libname in ('WORK');
15     ;
16     &dropstmt
17     ;
NOTE: Table WORK.DATA1 has been dropped.
NOTE: Table WORK.DATA2 has been dropped.
NOTE: Table WORK.DATA3 has been dropped.
18   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.01 seconds


19
20   %put >>>"&dropstmt"<<<;







Re: How to identify and delete empty datasets in an elegant manner

Postby RandyHerbison » Sun, 26 Mar 2006 07:15:46 GMT

entry libname :input :char;

dcl num rc;

init:
dsid=open('sashelp.vtable(where=(libname=upcase("' || libname || '") and
nobs=0))');
any=attrn(dsid,'ANY');
if any then do;
  do until(fetch(dsid)^=0);
    rc=delete('work.'||getvarc(dsid,varnum(dsid,'MEMNAME')));
  end;
end;
dsid=close(dsid);
return;

-----Original Message-----
From:  XXXX@XXXXX.COM  [mailto: XXXX@XXXXX.COM ]
On Behalf Of  XXXX@XXXXX.COM 
Sent: Friday, March 24, 2006 2:45 PM
To: Jiann-Shiun Huang;  XXXX@XXXXX.COM 
Subject: Re: Re: How to identify and delete empty datasets in an elegant
manner


Or, you might consider the folllowing simple SAS Component Language
program.

length memname $32;
init:
  submit continue;
     proc contents data=newtable._all_ dir noprint out=lib_contents;
     run;
  endsubmit;
lib_contents_dsid=open('lib_contents','i');
do until(fetch(lib_contents_dsid)^=0);
   temp_dsid=open('sashelp.'||memname,'v');
   if attrn(temp_dsid,'nobs')=0 then do;
      close_rc=close(temp_dsid);
      delete_rc=delete('newtable.'||memname);
  end;
end;
close_rc=close(lib_contents_dsid);
return;



Re: How to identify and delete empty datasets in an elegant manner

Postby joewhitehurst » Sun, 26 Mar 2006 07:24:35 GMT

andy,

Nice work. I noticed that my solution would not have worked because I left out a call set statement, so values of memname on the contents dataset would never have been moved into the SCL nonwindow variable of the same name causing the delete function to fail.

Joe

Re: How to identify and delete empty datasets in an elegant manner

Postby Shelley.Baxter » Sun, 26 Mar 2006 11:44:15 GMT





Thank you for the help. I can see that the Shiling approach is the most
compact and elegant. I will keep the dictionary vs vtable distinction in
mind. I have thought about learning SCL since I won a book about it as a
door prize and keep finding myself in that documentation area and that's
the only way something can be done. But if I bunny trail off on that now I
will never get the project done!

Re: How to identify and delete empty datasets in an elegant manner

Postby BenDray » Tue, 28 Mar 2006 19:05:41 GMT

Hi Joe/Randy,

I like the look of the SCL program above - however I've never used SCL
and just tried to copy/paste into a SAS session but it's not working.
Can you tell me how to run this SCL code please??

Many thanks!!

Ben


Re: How to identify and delete empty datasets in an elegant manner

Postby joewhitehurst » Wed, 29 Mar 2006 02:29:47 GMT

Ben,

You must place the code in a SAS Catalog as member type SCL and compile it
before you can execute it.

Joe

-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of BenDray
Sent: Monday, March 27, 2006 5:06 AM
To:  XXXX@XXXXX.COM 
Subject: Re: How to identify and delete empty datasets in an elegant manner

Hi Joe/Randy,

I like the look of the SCL program above - however I've never used SCL
and just tried to copy/paste into a SAS session but it's not working.
Can you tell me how to run this SCL code please??

Many thanks!!

Ben

Similar Threads:

1.How to identify and delete empty datasets in an elegant

2.FW: How to identify and delete empty datasets in an elegant

3.FW: How to identify and delete empty datasets in an

4.deleting empty files in a catalog

Hi,
I am planning to make a macro that has a catalog name as parameter and 
delete all empty files in that catalog.

%macro delete_empty_files(catalog);
???
%mend;

Has anyone made something like that before and can share it with me ? I am 
using Windows XP if it matters.

Regards, Rune




5.Deleting empty columns

I have datasets with 50+ variables. However not all of the columns are
being used. What would be the most efficient way to delete all columns
that are completely empty?

Thanks,
Gerrit

6. Why doesn't this macro delete the empty dataset?

7. identifying two different datasets

8. Identifying observations not in other Datasets



Return to sas

 

Who is online

Users browsing this forum: No registered users and 99 guest