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"
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.
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.
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!
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.
quick correction it should be
CATX(' ',SUBSTR("&CY.",3,4),"&PERIOD.") AS PERIOD,
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.