I have a proc sql query containing year(date)=2018 in the where-clause and everything works normally. However, I needed to add a year(date) variable to select-clause in character format so I tried to add "put(year(date),16.)" but didnt get any results. I automatically started to search error in my where-condition but surprisingly didnt find any and confirmed that this new variable in select-clause causes the zero results (no error messages but only no results). Does anyone know why this happens? This doesnt happen in SQLServer. There seems to be connection between the year(date) variable in select-clause and where-clause although shouldnt ( I removed the year(date) from where-clause and got results again). Please see example below:
Works:
select id, year(date) from data1
where year(date)=2018
Doesnt yield any results nor error messages:
select id,put(year(date),16.) from data1
where year(date)=2018
Hi,
I still don't understand your problem well, but you want to convert numeric variable to characteristic variable in SQL, right??
Your format "16." in PUT function is what I don't know well. Anyway, how about this?
data test;
date=today(); year=year(date); output;
date=today()-100; year=year(date); output;
date=today()-1000; year=year(date); output;
run;
proc sql;
create table work.test_1 as
select *, put(year, best.)
from work.test
where year eq 2018
;
quit;
Hi,
I still don't understand your problem well, but you want to convert numeric variable to characteristic variable in SQL, right??
Your format "16." in PUT function is what I don't know well. Anyway, how about this?
data test;
date=today(); year=year(date); output;
date=today()-100; year=year(date); output;
date=today()-1000; year=year(date); output;
run;
proc sql;
create table work.test_1 as
select *, put(year, best.)
from work.test
where year eq 2018
;
quit;
@aleksi555 wrote:
I have a proc sql query containing year(date)=2018 in the where-clause and everything works normally. However, I needed to add a year(date) variable to select-clause in character format so I tried to add "put(year(date),16.)" but didnt get any results. I automatically started to search error in my where-condition but surprisingly didnt find any and confirmed that this new variable in select-clause causes the zero results (no error messages but only no results). Does anyone know why this happens? This doesnt happen in SQLServer. There seems to be connection between the year(date) variable in select-clause and where-clause although shouldnt ( I removed the year(date) from where-clause and got results again). Please see example below:
Works:
select id, year(date) from data1
where year(date)=2018
Doesnt yield any results nor error messages:
select id,put(year(date),16.) from data1
where year(date)=2018
You did not give SQL the Name of the resulting value: put(year(date),16.) as YearChar
or what ever the name of the result should be. I would question using 16 characters though.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.