SAS PROC SQL String Manipulation

0
votes

Can I convert a string column formatted as "YYYY-MM" from SQL back into a Date that uses text. (ie. January, 2014) as a new column using proc SQL?

Initially my source is 2014-01 and I would like every row to be converted to the respective month and year as shown. I have tried the Format option outside of the proc SQL table build, however I need it as a Macro date afterwards.

Thanks

3
charsasproc-sql
The answer is probably yes, but you aren't really explaining yourself very well. What is "a Date that uses text" - is that a formatted numeric column? And when you reference SQL, is this just SAS's SQL or are you putting this in SQL Server (or getting it from there) or similar? – Joe
It's formatted that way, but is it a SAS date or a text field that is YYYY-MM? – Reeza
The SQL I am referring to is PROC SQL( Only in SAS). Secondly the text format of the char to date conversion would read : January, 2014 from the original Char value of 2014-01 – Tinkinc
@Reeza The Column field YYYY-MM is a text field I want to convert to a name field. – Tinkinc

3 Answers

0
votes

Here's one option that uses a custom format. First convert to date using INPUT() with the ANYDTDTE. format. Then you can either display the variable with the format or you can convert it to a new character variable with the format applied.

/*Create your own format definition*/
proc format;
picture monyyc_fmt (default=25)
low - high = "%B, %Y" (datatype=date);
run;

data want;
   str='2014-12';
   date=input(str,anydtdte.);
   format date monyyc_fmt.;
   want=put(date, monyyc_fmt.);
   put (_all_) (=/);
run;

proc print data=want;
run;

Results are:

                    Obs      str           date              want

                    1     2014-12    December, 2014    December, 2014

References for the custom date format: http://www2.sas.com/proceedings/sugi31/243-31.pdf

0
votes

Not sure what PROC SQL has to do with the issue of converting the value. One way would be to convert your string to date using INPUT() and then use the CATX(), PUT() and YEAR() functions and the MONNAME. format to build the new string.

data _null_;
   str='2014-12';
   date=input(str,anydtdte.);
   format date date9.;
   want=catx(', ',put(date,monname.),year(date));
   put (_all_) (=/);
run;

You could skip the middleman and just parse the string yourself to get the year number. This might be easier to code inside an SQL statement.

want=catx(', ',put(input(str,anydtdte.),monname.),scan(str,1,'-'));
-1
votes

In SQL, consider the next example:

-- The table holding the result

CREATE TABLE #myTable(stringDate CHAR(7), dateValue DATETIME, newStringDate VARCHAR(20))

-- The values in original format

INSERT INTO #myTable(stringDate) SELECT '2014-01'

INSERT INTO #myTable(stringDate) SELECT '2014-02'

INSERT INTO #myTable(stringDate) SELECT '2014-03'

INSERT INTO #myTable(stringDate) SELECT '2015-01'

INSERT INTO #myTable(stringDate) SELECT '2015-02' GO

-- All months have a first day, so conveting "2014-01" to date is just removing "-" and adding "01"

UPDATE #myTable SET dateValue = CAST(REPLACE(stringDate, '-', '') + '01' AS DATETIME) GO

-- When we have the dates, you can tranform it to the format you want

UPDATE #myTable SET newStringDate = DATENAME(MONTH, dateValue) + ', ' + CAST(YEAR(dateValue) AS VARCHAR) GO

SELECT * FROM #myTable