topic Re: select a number field where clause in proc sql in SAS Programming

select a number field where clause in proc sql

nshTi62 — Tue, 24 Mar 2020 19:58:41 GMT

Hi I have simple query in Proc SQL to look for all ID = 0, but my result included both 1 and 0. The table is in a SQL Server. My query is below

Proc SQL;

Select StudentID

from TBL_Student

where StudentID = 0;

quit;

I had tried to the different in where clause but did not work :

where put(studentid, $1.) = '0'

where put(studentid, best.) = 0

where studentid = put(0, $1.)

where studentid = put(0, 1.)

Please help.

novinosrin — Tue, 24 Mar 2020 20:07:50 GMT

Hi @nshTi62 Assuming I understand your requirement


Proc SQL;

  Select *  /*all columns*/

from TBL_Student
group by StudentID
having count(distinct StudentID) = 1 and studentid=0;

quit;

Or if it's not, post a sample of what you HAVE and what you WANT

PGStats — Tue, 24 Mar 2020 20:48:29 GMT

Or, if studentId is a character field, try

Proc SQL;
Select *
from TBL_Student
where input(StudentID, best.) = 0;
quit;

Kurt_Bremser — Wed, 25 Mar 2020 08:30:12 GMT

Whenever something does not meet your expectations, post the log. Copy/paste the log into a window opened with the </> button.