Good morning! I'm here again trying to sharpen up my coding skills. So i'm creating a table from another table I just created. Here is my code:
PROC SQL;
CREATE TABLE obhdata.suicidegrant_07102017 AS
SELECT *, CLR_Recip_Parish as Par, propcase(LPAR_Parish_Desc) as Parish
FROM suicideclaim
where substr(Dx10_1) in ('X40','X41','X42','X46','X47','Y10','Y11',
'Y12','Y16','Y17','Y870','T39', 'T40', 'T423', 'T424', 'T427', 'T43', 'T509',
'T58', 'X44')
OR substr(Dx10_1) BETWEEN 'X60' AND 'X84'
OR Dx10_2 = 'T1491'
group by Recip_Par;
quit;
The errors I received are
ERROR: Function SUBSTR requires at least 2 argument(s).
ERROR: Function SUBSTR requires at least 2 argument(s).
ERROR: The following columns were not found in the contributing tables: CLR_Recip_Parish, LPAR_Parish_Desc.
I appreciate any and all feedback. Thanks!
SUBSTR needs to be told which portion of the variable you want so the basic syntax is Substr(var, startpositon, optionallength).
If you use Substr(var , 3) it is an instruction to start at the third character and select everything to the end of the variable, substr(var, 3, 5) says to start at position 3 and get 5 characters.
substr(Dx10_1, 1, 3) in ('X40','X41','X42','X46','X47','Y10','Y11',
'Y12','Y16','Y17','Y870','T39', 'T40', 'T423', 'T424', 'T427', 'T43', 'T509',
'T58', 'X44')
would compare the first 3 characters in the variable with those in the list if that was your desire.
If the desired characters may be anywhere in the varaible then you will may want another character search function.
SUBSTR needs to be told which portion of the variable you want so the basic syntax is Substr(var, startpositon, optionallength).
If you use Substr(var , 3) it is an instruction to start at the third character and select everything to the end of the variable, substr(var, 3, 5) says to start at position 3 and get 5 characters.
substr(Dx10_1, 1, 3) in ('X40','X41','X42','X46','X47','Y10','Y11',
'Y12','Y16','Y17','Y870','T39', 'T40', 'T423', 'T424', 'T427', 'T43', 'T509',
'T58', 'X44')
would compare the first 3 characters in the variable with those in the list if that was your desire.
If the desired characters may be anywhere in the varaible then you will may want another character search function.
Remark to @ballardw's answer:
Some of the desired values are 4 characters, not 3, so you can :
substr(Dx10_1, 1, 3) in ('X40','X41','X42','X46','X47','Y10','Y11',
'Y12','Y16','Y17','Y870','T39', 'T40', 'T43','T58', 'X44') OR
substr(Dx10_1, 1, 4) in ('T423', 'T424', 'T427', 'T509')
We can't tell you more than the ERROR messages already do. You tried to access columns that are not present, and the substr function requires at least a second argument.
Since you used the asterisk (which takes all incoming columns), naming additional columns does not really make sense (unless you create new columns with calculated values).
For further help, post example data (use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset into a data step for posting) and the expected result.
For posting code, use the "little running man" (7th above the posting window) icon.
ERROR: Function SUBSTR requires at least 2 argument(s).
ERROR: Function SUBSTR requires at least 2 argument(s).
From the docs (note I can never seem to find 9.4 docs on anything):
https://v8doc.sas.com/sashtml/lgref/z0212264.htm
ERROR: The following columns were not found in the contributing tables: CLR_Recip_Parish, LPAR_Parish_Desc.
In the dataset suicideclaim these variables do no exist.
From my side, there doesn't appear to be any need for a group by in this clause, your second where cause will not do what you think it will do, and your casing/indentation is all over the place - makes it real hard to read:
proc sql; create table OBHDATA.SUICIDEGRANT_07102017 as select *, CLR_RECIP_PARISH as PAR, propcase(LPAR_PARISH_DESC) as PARISH from SUICIDECLAIM where substr(DX10_1,1,3) in ('X40','X41','X42','X46','X47','Y10','Y11', 'Y12','Y16','Y17','Y870','T39','T40','T423', 'T424','T427','T43','T509','T58','X44') or (substr(DX10_1,1,1)="X" and input(substr(DX10_1,2,3),best.) between 60 AND 84) or DX10_2='T1491'; quit;
So i'm back with a similar question. Actually it's two parts. One, here is my code and here is the error message
Proc SQL;
create table zerosuicidegrant as
select distinct *,
FROM suicidegrant_stratified
_______________________
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =,
>, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE,
LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
where substr(Diag_1, 1, 3) in ('X40','X41','X42','X44','X46','X47','Y10','Y11','Y12','Y16','Y17','T39', 'T40', 'T43', 'T58')
OR substr(Diag_1,1,4) in ('Y870','T423', 'T424', 'T427', 'T509')
OR substr(Diag_1, 1,3) BETWEEN 'X60' AND 'X84'
OR Diag_1 = 'T1491';
quit;
Proc SQL;
create table zerosuicidegrant as
select distinct *,
FROM suicidegrant_stratified
where substr(Diag_1, 1, 3) in ('X40','X41','X42','X44','X46','X47','Y10','Y11','Y12','Y16','Y17','T39', 'T40', 'T43', 'T58')
OR substr(Diag_1,1,4) in ('Y870','T423', 'T424', 'T427', 'T509')
OR substr(Diag_1, 1,3) BETWEEN 'X60' AND 'X84'
OR Diag_1 = 'T1491';
quit;
_______________________
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =,
>, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE,
LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
Also, how do I remove duplicates? THANK YOU all for your help! I'm still a novice, but I want to get better.
Please post a new question for new questions and don't reopen answered questions.
For your point you have a comma after the star which is causing the error, comma indicates there is another column
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.