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

concatenating the substring of a global variable in proc sql

joseatmc1 — Mon, 11 Dec 2017 14:59:14 GMT

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"

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

RW9 — Mon, 11 Dec 2017 15:16:37 GMT

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.

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

joseatmc1 — Mon, 11 Dec 2017 15:24:39 GMT

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!

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

joseatmc1 — Mon, 11 Dec 2017 15:27:22 GMT

quick correction it should be

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

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

RW9 — Mon, 11 Dec 2017 15:37:25 GMT

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.