Help using Base SAS procedures

concatenating the substring of a global variable in proc sql

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

concatenating the substring of a global variable in proc sql

I have what I thought was a simple thing to do ,but ended up being more difficult.

 

I have a global variable called &CY.  that is = to "FY2017" and another &PERIOD. that is equal "11" (BOTH ARE CHARACTER VALUES).

 

in  a proc sql statement I'm trying to create a column called Period that concatenates the year part of FY2017 to the Period.

 below is the

 

PROC SQL;
CREATE TABLE SAS_DATA.CUST_PVM_PTD_&PERIOD.&FY. AS
SELECT "CUSTPVM" AS DATA_SOURCE,"COMPONENTS" AS PRICING_TYPE,"COMPONENTS" AS PRICING_TYPE_LESS_RECON,
CATX(' ',SUBSTR(&CY.,2,4),&PERIOD.) AS PERIOD, 

 

 

I'm getting the following errors

ERROR: The following columns were not found in the contributing tables: FY2017.

This isn't working  I'm not trying to concatenate and entire column, just want each row to say "201711"


Accepted Solutions
Solution
‎12-11-2017 10:24 AM
Super User
Super User
Posts: 9,599

Re: concatenating the substring of a global variable in proc sql

Posted in reply to joseatmc1

Please format code so that it is readable - i.e. not all uppercase, indented, new row for each line etc.  Trying to debug a solid line of shouting is not fun.  Anyways, you are resolving your macro to this:

CATX(' ',SUBSTR(FY2017,2,4),11)

As you can see, once resolution has happened, it now looks like you want to substr a variable called fy2017, and concat a variable 11 - which is invalid.  What you want is:

CATX(' ',SUBSTR("&CY.",2,4),"&PERIOD.")

Note the double quotes around them. 

View solution in original post


All Replies
Solution
‎12-11-2017 10:24 AM
Super User
Super User
Posts: 9,599

Re: concatenating the substring of a global variable in proc sql

Posted in reply to joseatmc1

Please format code so that it is readable - i.e. not all uppercase, indented, new row for each line etc.  Trying to debug a solid line of shouting is not fun.  Anyways, you are resolving your macro to this:

CATX(' ',SUBSTR(FY2017,2,4),11)

As you can see, once resolution has happened, it now looks like you want to substr a variable called fy2017, and concat a variable 11 - which is invalid.  What you want is:

CATX(' ',SUBSTR("&CY.",2,4),"&PERIOD.")

Note the double quotes around them. 

New Contributor
Posts: 3

Re: concatenating the substring of a global variable in proc sql

sorry RW9 . I'm new to all these boards I apologize for shouting. I had tried quotes before but I was using single quotes, because I thought single quotes allowed for the global variables/formulas. but I guess it's the double quotes that do that. right?

 

I think that is working correctly now.

 

 

thank you so much!

Super User
Super User
Posts: 9,599

Re: concatenating the substring of a global variable in proc sql

Posted in reply to joseatmc1

Yes, only double quote marks will allow macro information within a string to be resolved, singles quotes will just treat the whole bit as plain text.

New Contributor
Posts: 3

Re: concatenating the substring of a global variable in proc sql

quick correction  it should be

CATX(' ',SUBSTR("&CY.",3,4),"&PERIOD.") AS PERIOD,
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 184 views
  • 2 likes
  • 2 in conversation