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,
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: