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
Another, very simple, method to get rid of leading and trailing blanks in macro variables is this:
%let n=&n;
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 -
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
That's what the communities are meant for. Please accept the message that helped you most (or first) as the solution.
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
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
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
Another, very simple, method to get rid of leading and trailing blanks in macro variables is this:
%let n=&n;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.