How to identify and delete empty datasets in an elegant manner

sas

    Sponsored Links

    Next

  • 1. parenthesis appending
    Here is the code I was using to try and append the closing parenthesis to the dataset name with member appended. In this case when the members are associated with a certain field there is no dataset name in the original report but a standard dataset name as I show below: > 000088 DATA FTPINF; SET FTPCHKS; > 000089 FORMAT PDSN $CHAR54. ; > 000090 IF MISSING(FTPCMDS) AND MISSING(FTPSTRT) AND > 000091 MISSING(SDSN) AND MISSING(DSN) THEN DELETE; > 000092 IF FTPSTRT NE ' ' THEN DO; > 000093 TDSN='ENDP.PROD.SYSINLIB(' ; > 000094 TESTRT=FTPSTRT||')' ; > 000095 SDSN=TDSN||FTPSTRT ; > 000096 PDSN=TDSN||TESTRT ; > 000097 END; I had not looked at this for awhile. I now see that with the above code having executed, I missed seeing that it is getting a message that the TESTRT field is too long and it is being truncated. Assuming that I can correct my length problem I should be able to simplify the code to just concatenating the ')'. No wonder I would never see the closing parenthesis! I thought I was having a DUH moment and unfortunately I was correct. Sorry if I wasted everyone's time. Alina Alina Chu New York University
  • 2. Pulling data from table into data step or proc sql
    I want to know if there is a way using PC SAS or EG if there is a way I can pull data directly out of a field to put into a WHERE clause in a data step or an SQL statement. To give a little background, I have created a table that houses about 1200 rows in it, with an index field, and a "Where" field that houses the actual where criteria that I want to pass into my data step. This table builds automatically from a set of criteria and (hopefully) will automate a process by which I need to run the same query many many times, based on different where criteria. Thanks
  • 3. Issue with PROC SQL
    Hi everyone, I'm having a problem using PROC SQL, that must be quite easy to identify but I haven't found a justification. I have two tables, table1 and table2. Table1 includes some observations from table2 plus other observations that aren't included in table2, the same as table2 regarding table1, i.e.: table2 has observations that are in table1 plus some other observations that aren't. Both tables have the same variables, including the -id- variable (string) that identify each observation. If the same observation is in Table1 and Table2, they have the same id. I'm trying to get a table (table3) that has the observations from table1 but excluding the observations that are in both table1 and table2. At first I tried the following SQL: proc sql; create table table3 as select a.* from table1 as a, table2 as b where a.id<>b.id; quit; I thought that this should do the trick, but I was wrong... The result from this SQL is a table that comprises the following observations: (table1 observations)*(table2 observations) - (observations that are both in table1 and table2). The result should have been: table1 observations - observations that are both in table1 and table2. Any ideas? This should be a simple procedure, but I can't find a solution... Best, Nuno
  • 4. SAS and MySQL
    I am trying to import a SAS file into MySQL but having difficulties. Has anybody attempted to do this before? I would rather not have to rely on creating Text files before I import them. I only have the local SAS ODBC license with Base SAS to do this. I have tried using MySQL FrontEnd software and importing it this way but it fails to recognise the tables. A message saying "ODBC does not support the requested properties". Cheers Colin
  • 5. time with missing year
    I have a data set with some missing value in year data d1; input year1 month1 day1 year2 month2 day2 cards; 2006 7 10 2006 10 5 . 6 30 . 12 4 . 3 2 . 8 10 ; I want to calculate the days between year2 month2 day2 and year1 month1 day1. if any year value is missing i suppose year2=year1. how can I do it? Thanks Jeff

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 39 guest