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.
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;
Use dictionary.tables. It has a column containing number of columns. If you're looking for a specific column, use dictionary.columns.
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;
note you need to add seperated by ''
select count(memname) into: CompctTableSumOutCount separated by '' from dictionary.columns
WHERE UPCASE(LIBNAME)="WORK" and memname = 'COMBINEDPCTTABLESUMMARYOUT';
You need separated by to remove the extra white space after the value.
@DavidPhillips2 wrote:
You need separated by to remove the extra white space after the value.
That is what the TRIMMED keyword is for.
@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.
Here is a nice article about using dictionary tables and views:
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
Why not just proc contents?
Just to avoid an Error if there are no columns in the dataset.
Good thinking.
I'm running into a problem where the work table stays around beyond each run of the program.
/**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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.