topic Re: If Table Has a Column in SAS Programming

If Table Has a Column

DavidPhillips2 — Wed, 31 Oct 2018 17:32:11 GMT

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.

Re: If Table Has a Column

Kurt_Bremser — Wed, 31 Oct 2018 17:41:30 GMT

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

Re: If Table Has a Column

PaigeMiller — Wed, 31 Oct 2018 17:43:15 GMT

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;

Re: If Table Has a Column

pink_poodle — Wed, 31 Oct 2018 17:47:17 GMT

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

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

Re: If Table Has a Column

r_behata — Wed, 31 Oct 2018 17:51:08 GMT

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

Re: If Table Has a Column

novinosrin — Wed, 31 Oct 2018 17:55:03 GMT

Why not just proc contents?

Re: If Table Has a Column

r_behata — Wed, 31 Oct 2018 17:59:34 GMT

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

Re: If Table Has a Column

novinosrin — Wed, 31 Oct 2018 18:01:38 GMT

Good thinking.

Re: If Table Has a Column

DavidPhillips2 — Wed, 31 Oct 2018 18:02:39 GMT

note you need to add seperated by ''

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

Re: If Table Has a Column

novinosrin — Wed, 31 Oct 2018 18:04:13 GMT

HI @DavidPhillips2 why separated by

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

Re: If Table Has a Column

DavidPhillips2 — Wed, 31 Oct 2018 19:17:57 GMT

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

Re: If Table Has a Column

DavidPhillips2 — Wed, 31 Oct 2018 19:24:40 GMT

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

Re: If Table Has a Column

Tom — Wed, 31 Oct 2018 19:46:01 GMT

@DavidPhillips2 wrote:

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

That is what the TRIMMED keyword is for.

Re: If Table Has a Column

Ksharp — Thu, 01 Nov 2018 13:15:54 GMT


/**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;

Re: If Table Has a Column

PaigeMiller — Thu, 01 Nov 2018 13:59:48 GMT

@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.