topic Re: Urgent in SAS Procedures

Urgent

deleted_user — Tue, 16 Sep 2008 10:37:15 GMT

Hi everyone,

I really need Help in this one.

I need to list all the non empty columns of all tables in a libname.

How can I do this?

Thanks in advance

Re: Urgent

LinusH — Tue, 16 Sep 2008 10:56:43 GMT

I suggest that you build a macro that first read out all tables (and their column names) for a specific libname, using DICTIONARY.TABLES (from PROC SQL), then maybe do PROC FREQ on them to find out which columns has nonmissing values.

Regards,
Linus

Re: Urgent

deleted_user — Tue, 16 Sep 2008 11:01:26 GMT

Thank you very much for your quick answer, since this question is really urgent.

Since I am a newbie at SAS programming, I have one more question.

After making my first table (Table, Columns) how can I use it as an input in the from condition for another query?

Can you give me an example, it would be great.

Thanks in advance.

Re: Urgent

sbb — Tue, 16 Sep 2008 12:15:17 GMT

The SAS support website http://support.sas.com/ has a wealth of examples, samples and also DOC references.

Scott Barry
SBBWorks, Inc.

An Introduction to SAS PROC SQL
http://www2.sas.com/proceedings/sugi27/p070-27.pdf

SAS-hosted Documentation:
http://support.sas.com/documentation/

SAS Support website:
http://support.sas.com/

Re: Urgent

deleted_user — Tue, 16 Sep 2008 12:51:39 GMT

Thank you very much for the resources.

Re: Urgent

deleted_user — Wed, 17 Sep 2008 13:58:40 GMT

for now I am trying a personnal solution:

proc sql print;
select distinct T.memname into :nomesTabelas separated by ' '
from DICTIONARY.COLUMNS C,
DICTIONARY.TABLES T
where C.libname = 'test' AND T.libname = 'test' and
C.memname = T.memname
;

proc sql print;
select count(distinct T.memname) into :nTabelas separated by ' '
from DICTIONARY.COLUMNS C,
DICTIONARY.TABLES T
where C.libname = 'test' AND T.libname = 'test' and
C.memname = T.memname
;

%put &nomesTabelas;

data tabelas;
array tabelas {&nTabelas} &nomesTabelas;
run;

after gathering all the table names in the libname I want to iterate
it a select all columns to check if they are empty.

Two more questions:

1 - How can I doi that? I am trying like this:

data _null_;
do i = 1 to &nTabelas;
proc sql print;
select name into :nomesColunas separated by ' '
from DDS..&tabelas;
end;
run;

but it doesnt seems to work,

2 - How to find if the column is empty in the dataset?

Thanks in advance.

Re: Urgent

SAS_user — Wed, 17 Sep 2008 14:06:43 GMT

I have a question what is an empty column?

All datasets columns are listen in a view sashelp.vcolumn.

Re: Urgent

sbb — Wed, 17 Sep 2008 16:36:13 GMT

Might I suggest you create a new discussion thread/post when you have a different question. Also, a SUBJECT that relates to your question would be helpful.

SAS variables are either NUMERIC or CHARACTER in type. SAS NUMERIC type variables can have a value/state considered to be a "missing value". SAS CHARACTER variables can have a value/state some consider to be "missing" value but actually the value is either blank or non-blank. The SAS MISSING function can be applied to either CHARACTER or NUMERIC type variables. Some individuals relate SAS environment terminology to other DBMS nomemclature -- so I would say that a reference to an "empty" column is either a blank or missing value for a particular SAS variable.

Scott Barry
SBBWorks, Inc.

Recommended reading on SAS variables:

http://support.sas.com/documentation/cdl/en/lrcon/59522/HTML/default/a000998827.htm

Re: Urgent

Cynthia_sas — Wed, 17 Sep 2008 20:42:02 GMT

Hi:
About this piece of your question:
[pre]
data _null_;
do i = 1 to &nTabelas;
proc sql print;
select name into :nomesColunas separated by ' '
from DDS..&tabelas;
end;
run;

[/pre]

You cannot have a PROC SQL step inside a DATA step program. You must investigate other ways (such as a macro program which contains your PROC SQL code inside a macro do loop or a DATA step program which performs a CALL EXECUTE).

In general, SAS programs are composed of PROCEDURE steps and DATA steps. It is not appropriate to put an SQL step inside a DATA step in open code. When you got the error message about having an unclosed DO block, that was SAS trying to show you that the beginning of the PROC SQL step caused the compiler to detect that the DO block in the DATA step program had not "finished":
[pre]
12 data _null_;
13 do i = 1 to &nTabelas;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds

14 proc sql print;
-
117
ERROR 117-185: There was 1 unclosed DO block.

15 select name into :nC separated by ' '
16 from sashelp.class;
17 end;
---
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

18 run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.51 seconds
cpu time 0.09 seconds
[/pre]

If you DO set up code to execute (via CALL EXECUTE), it executes AFTER the data step has finished building the appropriate statements.

I usually recommend a more conservative approach which would be to start with a macro program and understand how that works first before jumping into CALL EXECUTE.

The macro facility documentation is quite thorough and contains examples of writing programs that execute based on iterations through a macro %DO loop (as opposed to a data step DO loop).

This paper is also a good resource for people who are just learning about macro processing: http://www2.sas.com/proceedings/sugi28/056-28.pdf

cynthia

Re: Urgent

SAS_user — Thu, 18 Sep 2008 07:13:15 GMT

Sorry if i was misunderstood. But the topic in this thread was:

I need to list all the non empty columns of all tables in a libname.

So i wanted to ask Eikon, how he describes a phrase "empty column".

Re: Urgent

sbb — Thu, 18 Sep 2008 13:05:54 GMT

My apology to the poster "SAS user" -- I read the reply incorrectly, not noticing the sender was different than the original.

Scott