CONCATENATING OPERATOR FOR PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

CONCATENATING OPERATOR FOR PROC SQL

In a query I need to join the Year & Month of a field but with ampersand (&) used for variables what would be the correct way to present the following:

PROC SQL;

CREATE TABLE RESULTS AS SELECT DPD_BUCKET, YEAR(MIS_MONTH) & MONTH(MIS_MONTH) AS DATA_MONTH FROM FAQ;

QUIT;


Accepted Solutions
Solution
‎09-18-2013 07:41 AM
Super User
Posts: 5,255

Re: CONCATENATING OPERATOR FOR PROC SQL

catx('&',put(YEAR(MIS_MONTH),4.), put(MONTH(MIS_MONTH),2.))

You could also try to build your own format (like the YYMMxw. format)

Data never sleeps

View solution in original post


All Replies
Solution
‎09-18-2013 07:41 AM
Super User
Posts: 5,255

Re: CONCATENATING OPERATOR FOR PROC SQL

catx('&',put(YEAR(MIS_MONTH),4.), put(MONTH(MIS_MONTH),2.))

You could also try to build your own format (like the YYMMxw. format)

Data never sleeps
Contributor
Posts: 51

Re: CONCATENATING OPERATOR FOR PROC SQL

Dear Linus,

Please see if you can provide me a complete syntax to use a custom format of my own in this regard in PROC SQL as you have recommended, i.e. YYMMxw. format as the concatenation via the catx function leads to month lower than 10 to be represented in single digits. In other words how to represent a month like Jan, Feb as 2012_01, 2012_02 instead of 2012_1, 2012_2 respectively.

Thanx again!

Super User
Posts: 5,255

Re: CONCATENATING OPERATOR FOR PROC SQL

See the DATATYPE= option to the PICTURE statement in PROC FORMAT, combined with the datatype directives:

Base SAS(R) 9.4 Procedures Guide

Data never sleeps
Super User
Posts: 5,255

Re: CONCATENATING OPERATOR FOR PROC SQL

Another question: does your MIS_MONTH contain SAS date values? If not, you can't use SAS date functions right off.

Data never sleeps
Occasional Contributor
Posts: 8

Re: CONCATENATING OPERATOR FOR PROC SQL

Hi,

Year and month will have & as connector.

PROC SQL;

CREATE TABLE RESULTS AS SELECT DPD_BUCKET, catx('&',YEAR(MIS_MONTH), MONTH(MIS_MONTH) ) AS DATA_MONTH FROM FAQ;

QUIT;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2779 views
  • 8 likes
  • 3 in conversation