DATA Step, Macro, Functions and more

Create a new column from existing column in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Create a new column from existing column in proc sql

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

 


Accepted Solutions
Solution
4 weeks ago
PROC Star
Posts: 1,571

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

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;

View solution in original post


All Replies
PROC Star
Posts: 1,571

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

[ Edited ]

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;
Super Contributor
Super Contributor
Posts: 266

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

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.  

 

 

Solution
4 weeks ago
PROC Star
Posts: 1,571

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

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;
Occasional Contributor
Posts: 8

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

Posted in reply to novinosrin

Thanks a lot for the solution.

It works perfectly.

ciao

massimo

 

 

Super Contributor
Super Contributor
Posts: 266

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

Posted in reply to novinosrin

Apparently you are a mind reader.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 148 views
  • 4 likes
  • 3 in conversation