topic Re: ERROR 22-322/ERROR 76-322: Macro Variable &n generates random white spaces in SAS Programming

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

Debugger — Mon, 17 Feb 2020 12:24:04 GMT

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

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

ed_sas_member — Mon, 17 Feb 2020 12:33:55 GMT

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 -

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

yabwon — Mon, 17 Feb 2020 12:50:28 GMT

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

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

Kurt_Bremser — Mon, 17 Feb 2020 12:51:45 GMT

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

%let n=&n;

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

Debugger — Mon, 17 Feb 2020 13:29:12 GMT

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

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

Kurt_Bremser — Mon, 17 Feb 2020 13:54:12 GMT

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

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

Debugger — Mon, 17 Feb 2020 15:42:05 GMT

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

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

yabwon — Mon, 17 Feb 2020 17:12:15 GMT

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