concatenating the substring of a global variable in proc sql

[joseatmc1]

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"

[RW9 · Accepted solution]

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. 

[joseatmc1]

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!

[RW9]

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.

[joseatmc1]

quick correction  it should be

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