topic Re: Help with SAS PROC SQL errors in SAS Programming

Help with SAS PROC SQL errors

Mjooda — Wed, 23 Mar 2022 14:12:30 GMT

proc sql;
create table Brooking as
select count (CAGE),
case
when( age< 5) then 0
when( 5<=age<14) then 1
when(15<=age<24) then 2
when(25<=age<34) then 3
when(35<=age<44) then 4
when(45<=age<54) then 5
when(55<=age<64) then 6
when(65<=age<74) then 7
when(75<=age<84) then 8
when(85<=age) then 9
else .
end as age_group
from _EXP1_.death2020;
group by CRESIDZIP_EXT;
quit;

Good morning!!!I tried using the code that was been helped with yesterday and this was the error coming out. I'm not sure why

but then if I use another data file, it brings out a table but also with an error. pls kindly help

proc sql;
create table Brooking as
select count (CDATEOFBIRTH),
case
when( age< 5) then 0
when( 5<=age<14) then 1
when(15<=age<24) then 2
when(25<=age<34) then 3
when(35<=age<44) then 4
when(45<=age<54) then 5
when(55<=age<64) then 6
when(65<=age<74) then 7
when(75<=age<84) then 8
when(85<=age) then 9
else '9'
end as CDATEOFBIRTH
from _EXP0_.dt2015final;
quit;

Re: Help with SAS PROC SQL errors

mkeintz — Wed, 23 Mar 2022 14:17:02 GMT

You have the following error message in your first example

Expression using less than (<) has components that are of different data types.

The only code I see with a "<" compares the variable age to a set of numeric values. Which in turn suggests that age is being stored as a character value. Check your proc contents of the data set.

As to the second error: The group by clause should be part of the select statement, but you separated the group by from the select with a semi-colon. Remove it.

Re: Help with SAS PROC SQL errors

PaigeMiller — Wed, 23 Mar 2022 14:19:11 GMT

Please show us the ENTIRE log for each PROC SQL, as text (not screen capture) by copying the log and pasting it into the window that appears when you click on the </> icon. DO NOT SHOW US PORTIONS OF THE LOG!

Re: SAS

ballardw — Wed, 23 Mar 2022 14:19:26 GMT

SAS has two data types, numeric and character. When you get any comment about "different types" it means one of the values you used is character and the other is numeric. So the errors come because you compare a character value to numeric.

The bit about different data sets do not behave the same means that the variable for some reason is numeric in one set and character in another. The single most likely cause of this that you are bringing data into SAS using Proc Import or a wizard/tool that uses Proc Import and there is something in the source file that makes SAS think one of the source columns should be treated as character.

Common causes values that contain text like "NA" "NULL" or "MISSING" or some character attached to a number such as "<10" or possibly a range indicated like "10-13".

If you are reading multiple source files that should have the same structure and variable then Data step code is the preferred method to guarantee that each of the created SAS data sets has the same behavior.

The bit about import may be more critical if your files start as spread sheets as those do not impose many rules on what ends up in any given column. Also, if you have some text files, such as CSV, that are opened by spreadsheet software and then saved you may have had the contents of columns actually changed.