BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
joseatmc1
Fluorite | Level 6

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"

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

quick correction  it should be

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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