BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
10 REPLIES 10
LinusH
Tourmaline | Level 20
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
Data never sleeps
deleted_user
Not applicable
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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/
deleted_user
Not applicable
Thank you very much for the resources.
deleted_user
Not applicable
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.
SAS_user
Calcite | Level 5
I have a question what is an empty column?

All datasets columns are listen in a view sashelp.vcolumn.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
SAS_user
Calcite | Level 5
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".
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
My apology to the poster "SAS user" -- I read the reply incorrectly, not noticing the sender was different than the original.

Scott
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 964 views
  • 0 likes
  • 5 in conversation