topic Re: Create a new column from existing column in proc sql in SAS Programming

Create a new column from existing column in proc sql

max_ros — Thu, 24 May 2018 14:41:53 GMT

Good aftenoon at all,

i need to create a new column mandatory in proc sql from a datasets where the existing column contains years in numbers.

here below my query that doesn't "run":

proc sql;

create table new as

select "31/12"!!year <--- the problem as MY_DATE

from old; /* where old db contains a variable "year" (year values are : 2000,2001,2002, etc - numerical format -)

quit;

I need to know how "to pass" correctly values of year.

Thanks in advance

massimo

Re: Create a new column from existing column in proc sql

novinosrin — Thu, 24 May 2018 14:56:49 GMT

do you mean you are after

proc sql;

create table new as

select "31/12/"!!put(year,8. -l)  as MY_DATE

from old; /* where old db contains a variable "year" (year values are : 2000,2001,2002, etc - numerical format -)

quit;

Re: Create a new column from existing column in proc sql

HB — Thu, 24 May 2018 14:53:44 GMT

It is unclear what you are trying to do. Some actual data provided in the form of a data step and the desired result from that data would go a long way.

Without knowing much more, I would suggest your solution will involve the year() and/or the datepart() functions.

Re: Create a new column from existing column in proc sql

novinosrin — Thu, 24 May 2018 14:57:38 GMT

a small demo if this may help:

data old;
do year=2000,2001,2002;
output;
end;
run;

proc sql;

create table new as

select "31/12/"!!put(year,8. -l)  as MY_DATE

from old;
quit;

Re: Create a new column from existing column in proc sql

max_ros — Thu, 24 May 2018 15:32:38 GMT

Thanks a lot for the solution.

It works perfectly.

ciao

massimo

Re: Create a new column from existing column in proc sql

HB — Thu, 24 May 2018 17:22:20 GMT

Apparently you are a mind reader.