questions about quotes in variables and parameters

[davmarol78]

Hi,

Sorry for my poor english i'm beginner with SAS and i am blocked with a stupid error in my program

I have troubles with values i want to pass in a SQL request with a variable :

%do x = 1 %to %EVAL(%sysfunc(COUNTC(&liste_tables,' ')) + 1); /* to pass all the table list */

%let curr_table = %scan(&liste_tables,&x);

create table travail as select * from &curr_table.;

/* works fine */

insert into compteurs select &curr_table. as table, count(*) as nb from travail

/* doesn't work because the value of curr_table is not a field in the table travail, i just want to pass the value of the table to insert it

I think it's because there are no quotes in my value, but i don't find how to put quotes, i tried quotes(&curr_table,"'") without success

thanks for your help, regards

[Amir]

Hi,

Thank you for sharing your code. So that we can try to help you better, please also carry out the following:

1. Submit options mprint symbolgen; to show what the macro is generating and then submit the code you posted again.
2. Post the full log of the code and any messages (especially errors) using the Insert Code icon "</>".
3. Please provide an example of the correct text that you want to see generated.

Thanks & kind regards,

Amir.

[davmarol78]

Hi Amir and thaks i hope to be clear

I have my dataset LISTE_TABLE which contains all the tables i want to scan

For my test it only contains the table "test_table"

The result is to have this dataset COMPTEURS :

table                 nb_avant

test_table        136850

with 136850 the number of rows of my table "test_table"

30         %MACRO test();
31         
32         proc sql;
33         	create table COMPTEURS(table char(40), nb_avant num(8), nb_apres_att num(8), nb_purge_att num(8), nb_apres_obt num(8),
33       ! nb_purge_obt num(8));
34         	select DATASET_A_TRAITER into :liste_tables separated by ' '
35         	from LISTE_TABLES;
36         quit;
37         
38         %do x = 1 %to %EVAL(%sysfunc(COUNTC(&liste_tables,' ')) + 1);
39         	%let curr_table = %scan(&liste_tables,&x);
40         	proc sql;
41         	insert into COMPTEURS (table, nb_avant)
42         			select &curr_table. as table, count(*) as nb_avant from TRAVAIL
43         	;
44         	quit;
45         %end;
46         
47         %MEND test;
48         
49         %test();
MPRINT(TEST):   proc sql;
MPRINT(TEST):   create table COMPTEURS(table char(40), nb_avant num(8), nb_apres_att num(8), nb_purge_att num(8), nb_apres_obt 
num(8), nb_purge_obt num(8));
NOTE: Table WORK.COMPTEURS created, with 0 rows and 6 columns.
MPRINT(TEST):   select DATASET_A_TRAITER into :liste_tables separated by ' ' from LISTE_TABLES;
MPRINT(TEST):   quit;
NOTE: PROCEDURE SQL a utilisé (Durée totale du traitement) :
      real time           0.00 seconds
      cpu time            0.01 seconds
      

SYMBOLGEN:  La macro-variable LISTE_TABLES est résolue en test_table 
SYMBOLGEN:  La macro-variable LISTE_TABLES est résolue en test_table 
SYMBOLGEN:  La macro-variable X est résolue en 1
MPRINT(TEST):   proc sql;
SYMBOLGEN:  La macro-variable CURR_TABLE est résolue en test_table
MPRINT(TEST):   insert into COMPTEURS (table, nb_avant) select test_table as table, count(*) as nb_avant from TRAVAIL ;
ERROR: Les colonnes suivantes sont introuvables dans les tables de contribution : test_table.
ERROR: Les colonnes suivantes sont introuvables dans les tables de contribution : test_table.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(TEST):   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL a utilisé (Durée totale du traitement) :
      real time           0.00 seconds
      cpu time            0.01 seconds
      
50         options mprint symbolgen;
51         
52         %LET _CLIENTTASKLABEL=;
53         %LET _CLIENTPROCESSFLOWNAME=;
54         %LET _CLIENTPROJECTPATH=;
55         %LET _CLIENTPROJECTPATHHOST=;
56         %LET _CLIENTPROJECTNAME=;
57         %LET _SASPROGRAMFILE=;
58         %LET _SASPROGRAMFILEHOST=;
59         
60         ;*';*";*/;quit;run;
3                                                          Le Système SAS                              09:12 Wednesday, July 9, 2025

61         ODS _ALL_ CLOSE;
62         
63         
64         QUIT; RUN;
65         

[Tom]

What are you trying to do with this complex equation?

 %EVAL(%sysfunc(COUNTC(&liste_tables,' ')) + 1)

Let's break it down. So first it counts how many times either of the two characters space and single quote appear in the macro variable.  Then it adds one.

Since you seem to be using it as the upper bound for a %DO loop that processes each item in a list why not just use the COUNTW() function to find out how many items are in the list instead?  Also make sure to use the same set of characters as delimiters in both the call to COUNTW() and the call to %SCAN().

%do x = 1 %to %sysfunc(COUNTW(&liste_tables,%str( )));
  %let curr_table = %scan(&liste_tables,&x,%str( ));

[yabwon · Accepted solution]

Hi @davmarol78 

it looks like you are looking for something like this:

%macro test(liste_tables);

%local x curr_table;

data compteurs; 
  length table $ 41 nb 8;
  stop;
run;

PROC SQL;
%do x = 1 %to %sysfunc(COUNTW(&liste_tables,%str( ))); /* to pass all the table list */
%let curr_table = %scan(&liste_tables,&x,%str( ));

create table travail as select * from &curr_table.;

/* works fine */

insert into compteurs select "&curr_table." as table, count(*) as nb from travail;

%end;

QUIT;

%mend test;

%test(sashelp.class sashelp.cars sashelp.iris)

proc print data = compteurs;
run;

but! if you are really interested in getting tables metadata, and they are SAS tables, consider using SQL's DICTIONARY tables, for example:

proc sql;
select memname, nobs from DICTIONARY.TABLES
where libname = "SASHELP";
quit;

you can filter them, and do it more efficient than opening each table and counting observations.

Bart

[davmarol78]

Hi Bart,

works fine ! thanks a lot i was stupidly blocked with this story of quotes...

in fact i don't just want to have the number of lines of my table, i have to use them for a further traitment