topic Re: concatenate variables in SAS Programming

concatenate variables

me55 — Thu, 20 Dec 2018 16:56:47 GMT

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):

Re: concatenate variables

novinosrin — Thu, 20 Dec 2018 16:59:51 GMT

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"

Re: concatenate variables

Reeza — Thu, 20 Dec 2018 17:02:03 GMT

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:

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):

Re: concatenate variables

novinosrin — Thu, 20 Dec 2018 17:03:35 GMT

And for concatenation,instead of the || operator in your

CHAR=&var3 || &var4

I would rather resolve without the concat || operator like

CHAR="&var3&var4"

Re: concatenate variables

me55 — Thu, 20 Dec 2018 17:09:58 GMT

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!