ERROR 22-322/ERROR 76-322: Macro Variable &n generates random white spaces

[Debugger]

Hi @ all,

I tried to generate Macro Variables via a Proc SQL in SAS EG however there seems to be a problem, when I try to generate several of these.

Code:

proc sql;
select count(*) into :n
from work.Tabelle1;
Quit;

proc SQL;
Select distinct NAME into :name1 - :name&n
from work.Tabelle1;
Quit;
Proc SQL;
select distinct Tabellenname into :Joinfeld1 - :Joinfeld&n
from work.Tabelle1;
Quit;

Logs:

27 proc sql;
28 select count(*) into :n
29 from work.Tabelle1;
30 Quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

31
32 proc SQL;
33 Select distinct NAME into :name1 - :name&n
34 from work.Tabelle1;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "N".
34 name 264
___
22
76
ERROR 22-322: Syntax error, expecting one of the following: ',', FROM, NOTRIM.

ERROR 76-322: Syntax error, statement will be ignored.

35 Quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

36 Proc SQL;
37 select distinct Tabellenname into :Joinfeld1 - :Joinfeld&n
38 from work.Tabelle1;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "N".
38 Joinfeld 264
___
22
76
ERROR 22-322: Syntax error, expecting one of the following: ',', FROM, NOTRIM.

ERROR 76-322: Syntax error, statement will be ignored.

39 Quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

[ed_sas_member]

Hi @Debugger 

Can you try to add the TRIMMED option in the below code. It will remove trailing blanks from the macro variable.

select count(*) into :n trimmed

In addition, you can also put only this:

Select distinct NAME into :name1 -

select distinct Tabellenname into :Joinfeld1 -

[Debugger]

Hi guys,

thank a lot for you quick help!!!

The first solution already worked! I have been sitting hours in front of this problem.

Cheers

Peter

[Kurt_Bremser]

That's what the communities are meant for. Please accept the message that helped you most (or first) as the solution.

[yabwon]

Hi @Debugger ,

As @ed_sas_member wrote, you can use hyphen(-) notation to get the list of macrovariables and then `&sqlobs.` may help to get the number of variables created:

proc sql noprint;
  select distinct age 
  into :age1-
  from sashelp.class
  ;
  %let N_age = &sqlobs.;
quit;
%put _user_;

One side note. You are counting all obs from the dataset in the first query and in the second you take distinct list, are those values the same?

All the best

Bart

[Debugger]

Hi;

@yabwon  they  should be the same value. The idea is that i want to get a list of distinct variables, that i can build a loop on, as I experienced however my code only count to the end of the oservations. any idea how i could change that? Is that possible with the Sqlobs?

Thanksin advance

[yabwon]

Hi @Debugger ,

to get variables list (not observations) I would try something like this:

data test;
  variable1 = 17;
  variable2 = 42;
  variable3 = 303;
run;

proc contents 
  data = test /* dataset from which you want variables list */
  out = variablesList(keep = name)
  noprint;
run;

data _null_;
 set variablesList end=eof nobs=nobs;
 call symputX(cats("name", _N_), name, "L");
 if eof;
 call symputX("n", nobs, "L");
run;

%put _user_;

All the best

Bart

[Kurt_Bremser · Accepted solution]

Another, very simple, method to get rid of leading and trailing blanks in macro variables is this:

%let n=&n;