okay, i am trying to build a table of differing date function results. the part i am having issues with is building a variable YYYY-xQ. i need that value in QIDC and QIDP but the concatenating is not working and i do not understand why. it is asking me for a mathematical operator in building a cat function. i have used catx, catt, cats and || and it fails. here is the code...
proc sql; create table DATES ( TYPE char(10) format=$10. informat=$10., DATE num format=MMDDYY8. informat=MMDDYY8., CHAR char(10) format=$10. informat=$10., NUM num format=6. informat=6.); quit; proc sql; insert into DATES set TYPE='YTRDC' set TYPE='YTRDP' set TYPE='QTRC' set TYPE='QTRP' set TYPE='YRC' set TYPE='YRP' set TYPE='QIDC' set TYPE='QIDP' set TYPE='QIDX' set TYPE='QIDP'; quit; proc sql; update DATES set DATE=intnx('month',today(),-1) where TYPE='YTRDC'; quit; proc sql; update DATES set DATE=intnx('month',today(),-4) where TYPE='YTRDP'; quit; proc sql; update DATES set NUM=qtr(intnx('month',today(),-1)) where TYPE='QTRC'; quit; proc sql; update DATES set NUM=qtr(intnx('month',today(),-4)) where TYPE='QTRP'; quit; proc sql; select NUM into :var1 from DATES where TYPE='QTRC'; quit; proc sql; select NUM into :var2 from DATES where TYPE='QTRP'; quit; proc sql; update DATES set CHAR=cats('',&var1,'Q') where TYPE='QTRC'; quit; proc sql; update DATES set CHAR=cats('',&var2,'Q') where TYPE='QTRP'; quit; proc sql; update DATES set NUM=year(intnx('month',today(),-1)) where TYPE='YRC'; quit; proc sql; update DATES set NUM=year(intnx('month',today(),-4)) where TYPE='YRP'; quit; proc sql; update DATES set CHAR=put(year(intnx('month',today(),-1)),4.) where TYPE='YRC'; quit; proc sql; update DATES set CHAR=put(year(intnx('month',today(),-4)),4.) where TYPE='YRP'; quit; proc sql; select CHAR into :var3 from DATES where TYPE='QTRC'; quit; proc sql; select CHAR into :var4 from DATES where TYPE='YRC'; quit; proc sql; select CHAR into :var5 from DATES where TYPE='QTRP'; quit; proc sql; select CHAR into :var6 from DATES where TYPE='YRP'; quit; proc sql; update DATES set CHAR=&var3 || &var4 where TYPE='QIDC'; quit; proc sql; update DATES set CHAR=cats('',&var5,'_',&var6) where TYPE='QIDP'; quit;
it is probably something simple but i dont get and the error is not helping...
GOPTIONS ACCESSIBLE; 24 proc sql; 25 update DATES 26 set 27 CHAR=&var3 || &var4 NOTE: Line generated by the macro variable "VAR3". 27 4Q _ 22 ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, WHERE, ||. 28 where TYPE='QIDC'; _____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 29 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 40.59k OS Memory 19872.00k Timestamp 12/20/2018 10:56:26 AM Step Count 153 Switch Count 68 Page Faults 7 Page Reclaims 21 Page Swaps 0 Voluntary Context Switches 217 2 The SAS System 08:12 Thursday, December 20, 2018 Involuntary Context Switches 0 Block Input Operations 64 Block Output Operations 8 30 31 proc sql; 32 update DATES 33 set 34 CHAR=cats('',&var5,'_',&var6) NOTE: Line generated by the macro variable "VAR5". 34 3Q _ 22 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 35 where TYPE='QIDP'; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 36 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):
Macro variables that are text can be either variable names or text values. If there are no quotes it's interpreted as a variable, if it's in quotes, it's text.
Put &var3, &var4 ... etc in quotes and it works fine.
proc sql;
update DATES
set
CHAR=catt("&var3", "&var4")
where TYPE='QIDC';
quit;
proc sql;
update DATES
set
CHAR=cats('',"&var5",'_',"&var6")
where TYPE='QIDP';
quit;
@me55 wrote:
okay, i am trying to build a table of differing date function results. the part i am having issues with is building a variable YYYY-xQ. i need that value in QIDC and QIDP but the concatenating is not working and i do not understand why. it is asking me for a mathematical operator in building a cat function. i have used catx, catt, cats and || and it fails. here is the code...
proc sql; create table DATES ( TYPE char(10) format=$10. informat=$10., DATE num format=MMDDYY8. informat=MMDDYY8., CHAR char(10) format=$10. informat=$10., NUM num format=6. informat=6.); quit; proc sql; insert into DATES set TYPE='YTRDC' set TYPE='YTRDP' set TYPE='QTRC' set TYPE='QTRP' set TYPE='YRC' set TYPE='YRP' set TYPE='QIDC' set TYPE='QIDP' set TYPE='QIDX' set TYPE='QIDP'; quit; proc sql; update DATES set DATE=intnx('month',today(),-1) where TYPE='YTRDC'; quit; proc sql; update DATES set DATE=intnx('month',today(),-4) where TYPE='YTRDP'; quit; proc sql; update DATES set NUM=qtr(intnx('month',today(),-1)) where TYPE='QTRC'; quit; proc sql; update DATES set NUM=qtr(intnx('month',today(),-4)) where TYPE='QTRP'; quit; proc sql; select NUM into :var1 from DATES where TYPE='QTRC'; quit; proc sql; select NUM into :var2 from DATES where TYPE='QTRP'; quit; proc sql; update DATES set CHAR=cats('',&var1,'Q') where TYPE='QTRC'; quit; proc sql; update DATES set CHAR=cats('',&var2,'Q') where TYPE='QTRP'; quit; proc sql; update DATES set NUM=year(intnx('month',today(),-1)) where TYPE='YRC'; quit; proc sql; update DATES set NUM=year(intnx('month',today(),-4)) where TYPE='YRP'; quit; proc sql; update DATES set CHAR=put(year(intnx('month',today(),-1)),4.) where TYPE='YRC'; quit; proc sql; update DATES set CHAR=put(year(intnx('month',today(),-4)),4.) where TYPE='YRP'; quit; proc sql; select CHAR into :var3 from DATES where TYPE='QTRC'; quit; proc sql; select CHAR into :var4 from DATES where TYPE='YRC'; quit; proc sql; select CHAR into :var5 from DATES where TYPE='QTRP'; quit; proc sql; select CHAR into :var6 from DATES where TYPE='YRP'; quit; proc sql; update DATES set CHAR=&var3 || &var4 where TYPE='QIDC'; quit; proc sql; update DATES set CHAR=cats('',&var5,'_',&var6) where TYPE='QIDP'; quit;it is probably something simple but i dont get and the error is not helping...
GOPTIONS ACCESSIBLE; 24 proc sql; 25 update DATES 26 set 27 CHAR=&var3 || &var4 NOTE: Line generated by the macro variable "VAR3". 27 4Q _ 22 ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, WHERE, ||. 28 where TYPE='QIDC'; _____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 29 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 40.59k OS Memory 19872.00k Timestamp 12/20/2018 10:56:26 AM Step Count 153 Switch Count 68 Page Faults 7 Page Reclaims 21 Page Swaps 0 Voluntary Context Switches 217 2 The SAS System 08:12 Thursday, December 20, 2018 Involuntary Context Switches 0 Block Input Operations 64 Block Output Operations 8 30 31 proc sql; 32 update DATES 33 set 34 CHAR=cats('',&var5,'_',&var6) NOTE: Line generated by the macro variable "VAR5". 34 3Q _ 22 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 35 where TYPE='QIDP'; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 36 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):
Difficult to look through all your code but a quick spot on the error log tells me
CHAR=&var3 || &var4
should be
CHAR="&var3"||"&var4"
And for concatenation,instead of the || operator in your
CHAR=&var3 || &var4
I would rather resolve without the concat || operator like
CHAR="&var3&var4"
Macro variables that are text can be either variable names or text values. If there are no quotes it's interpreted as a variable, if it's in quotes, it's text.
Put &var3, &var4 ... etc in quotes and it works fine.
proc sql;
update DATES
set
CHAR=catt("&var3", "&var4")
where TYPE='QIDC';
quit;
proc sql;
update DATES
set
CHAR=cats('',"&var5",'_',"&var6")
where TYPE='QIDP';
quit;
@me55 wrote:
okay, i am trying to build a table of differing date function results. the part i am having issues with is building a variable YYYY-xQ. i need that value in QIDC and QIDP but the concatenating is not working and i do not understand why. it is asking me for a mathematical operator in building a cat function. i have used catx, catt, cats and || and it fails. here is the code...
proc sql; create table DATES ( TYPE char(10) format=$10. informat=$10., DATE num format=MMDDYY8. informat=MMDDYY8., CHAR char(10) format=$10. informat=$10., NUM num format=6. informat=6.); quit; proc sql; insert into DATES set TYPE='YTRDC' set TYPE='YTRDP' set TYPE='QTRC' set TYPE='QTRP' set TYPE='YRC' set TYPE='YRP' set TYPE='QIDC' set TYPE='QIDP' set TYPE='QIDX' set TYPE='QIDP'; quit; proc sql; update DATES set DATE=intnx('month',today(),-1) where TYPE='YTRDC'; quit; proc sql; update DATES set DATE=intnx('month',today(),-4) where TYPE='YTRDP'; quit; proc sql; update DATES set NUM=qtr(intnx('month',today(),-1)) where TYPE='QTRC'; quit; proc sql; update DATES set NUM=qtr(intnx('month',today(),-4)) where TYPE='QTRP'; quit; proc sql; select NUM into :var1 from DATES where TYPE='QTRC'; quit; proc sql; select NUM into :var2 from DATES where TYPE='QTRP'; quit; proc sql; update DATES set CHAR=cats('',&var1,'Q') where TYPE='QTRC'; quit; proc sql; update DATES set CHAR=cats('',&var2,'Q') where TYPE='QTRP'; quit; proc sql; update DATES set NUM=year(intnx('month',today(),-1)) where TYPE='YRC'; quit; proc sql; update DATES set NUM=year(intnx('month',today(),-4)) where TYPE='YRP'; quit; proc sql; update DATES set CHAR=put(year(intnx('month',today(),-1)),4.) where TYPE='YRC'; quit; proc sql; update DATES set CHAR=put(year(intnx('month',today(),-4)),4.) where TYPE='YRP'; quit; proc sql; select CHAR into :var3 from DATES where TYPE='QTRC'; quit; proc sql; select CHAR into :var4 from DATES where TYPE='YRC'; quit; proc sql; select CHAR into :var5 from DATES where TYPE='QTRP'; quit; proc sql; select CHAR into :var6 from DATES where TYPE='YRP'; quit; proc sql; update DATES set CHAR=&var3 || &var4 where TYPE='QIDC'; quit; proc sql; update DATES set CHAR=cats('',&var5,'_',&var6) where TYPE='QIDP'; quit;it is probably something simple but i dont get and the error is not helping...
GOPTIONS ACCESSIBLE; 24 proc sql; 25 update DATES 26 set 27 CHAR=&var3 || &var4 NOTE: Line generated by the macro variable "VAR3". 27 4Q _ 22 ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, WHERE, ||. 28 where TYPE='QIDC'; _____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 29 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 40.59k OS Memory 19872.00k Timestamp 12/20/2018 10:56:26 AM Step Count 153 Switch Count 68 Page Faults 7 Page Reclaims 21 Page Swaps 0 Voluntary Context Switches 217 2 The SAS System 08:12 Thursday, December 20, 2018 Involuntary Context Switches 0 Block Input Operations 64 Block Output Operations 8 30 31 proc sql; 32 update DATES 33 set 34 CHAR=cats('',&var5,'_',&var6) NOTE: Line generated by the macro variable "VAR5". 34 3Q _ 22 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 35 where TYPE='QIDP'; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 36 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):
wow, i would have never guessed that. i guess quotes dont do in sas what they usually do. usually it is a literal reference but...the quotes worked perfectly, thanks for your help everyone!
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.