How can I convert a numeric column of type 200012 to a SAS date variable using PROC SQL?
I have a SAS dataset with a text field customer_id
and a numeric field month
in the format YYYYMM
. I am creating a view of this data, and I want to convert the data to a SAS standard date, which will (hopefully) persist on export. For example:
proc sql;
create view my_view as
select customer_id, month from raw_dataset;
quit;
proc export data = my_view
file = "out.dta"
dbms = stata replace;
quit;
Looking at the date documentation it looks like the number is in the form (albeit not in the datatype) YYMMN.
, but I want it to be in a format that SAS can work with as a date, not just a number, like c proc expand
.
I've seen a lot of questions using combinations of put
and datepart
, but since I don't want the variable to be a string and I don't have a datetime variable yet, I'm not sure how to apply them.
How can I convert this column to SAS date datatype when running this SQL query?
source to share
YYMMN.
is the correct information to use, and input
is exactly how you get it to be a date. Try it!
data want;
x='201403';
y=input(x,YYMMN6.);
put _all_;
run;
Of course y
now should probably be well formatted if you want to look at it, but it doesn't have to.
In PROC SQL
this works the same way.
proc sql;
create view want_v as
select x, y, input(x,yymmn6.) as z format=date9.
from want;
quit;
This also adds the format to make it look readable, but it's still a date variable, identical y
.
source to share