topic Re: Proc sql: Adding "where clause variable" to "select clause" inside put() yie in SAS Programming

Proc sql: Adding "where clause variable" to "select clause" inside put() yields zero results

aleksi555 — Tue, 30 Oct 2018 07:56:34 GMT

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

Re: Proc sql: Adding "where clause variable" to "select clause" inside put() yie

KentaMURANAKA — Tue, 30 Oct 2018 09:26:04 GMT

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;

Re: Proc sql: Adding "where clause variable" to "select clause" inside put() yie

ballardw — Tue, 30 Oct 2018 14:12:56 GMT

@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.