If Table Has a Column

[DavidPhillips2]

I'm trying to check if a table has a column.

proc sql;
select count(*) into: CompctTableSumOutCount from CombinedpctTableSummaryOut;

quit;

ERROR: Table WORK.COMBINEDPCTTABLESUMMARYOUT doesn't have any columns. PROC SQL requires each of its tables to have at least 1
column.

[Kurt_Bremser]

Use dictionary.tables. It has a column containing number of columns. If you're looking for a specific column, use dictionary.columns.

[PaigeMiller · Accepted solution]

Use PROC SQL to query the DICTIONARY.TABLES table.

proc sql;
    select nvar into :nvar from dictionary.tables where libname='WORK' and upcase(memname)='COMBINEDPCTTABLESUMMARYOUT';
quit;

[DavidPhillips2]

note you need to add seperated by ''

select count(memname) into: CompctTableSumOutCount separated by '' from dictionary.columns
WHERE UPCASE(LIBNAME)="WORK" and memname = 'COMBINEDPCTTABLESUMMARYOUT';

[novinosrin]

HI @DavidPhillips2 why separated by

to park the value of one var into one macro var???

[DavidPhillips2]

You need separated by to remove the extra white space after the value.

[Tom]

---

@DavidPhillips2 wrote:

You need separated by to remove the extra white space after the value.

---

That is what the TRIMMED keyword is for.

[PaigeMiller]

---

@DavidPhillips2 wrote:

You need separated by to remove the extra white space after the value.

---

In general you don't need this, for a specific use you might need to remove extra white space. But when I try this, I don't get white space after the expected zero in the macro variable.

[pink_poodle]

Here is a nice article about using dictionary tables and views:

http://www2.sas.com/proceedings/sugi30/070-30.pdf

[r_behata]

Try This :

%let dsid=%sysfunc(open(sashelp.class));
%let ncols=%sysfunc(attrn(&dsid,nvars));

%put &dsid &ncols;

%macro test;
%if &ncols > 0 %then %do;
proc sql ;
select count(*) into: CompctTableSumOutCount from sashelp.class;
quit;
%end;
%mend;

%test

[novinosrin]

Why not just proc contents?

[r_behata]

Just to avoid an Error if there are no columns in the dataset.

[novinosrin]

Good thinking. 

[DavidPhillips2]

I'm running into a problem where the work table stays around beyond each run of the program.

[Ksharp]

/**No variables and No observations**/
data class;
run;

%let dsid=%sysfunc(open(class));
%let ncols=%sysfunc(attrn(&dsid,any));
%let dsid=%sysfunc(close(&dsid));

%put &dsid &ncols;

/******No Observations****************/
data class;
 set sashelp.class;
 stop;
run;

%let dsid=%sysfunc(open(class));
%let ncols=%sysfunc(attrn(&dsid,any));
%let dsid=%sysfunc(close(&dsid));

%put &dsid &ncols;