Proc SQL Create Table question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Proc SQL Create Table question

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!


Accepted Solutions
Solution
‎07-14-2017 10:49 AM
Super User
Posts: 11,101

Re: Proc SQL Create Table question

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.

View solution in original post


All Replies
Solution
‎07-14-2017 10:49 AM
Super User
Posts: 11,101

Re: Proc SQL Create Table question

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.

Trusted Advisor
Posts: 1,459

Re: Proc SQL Create Table question

[ Edited ]

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')

 

Super User
Posts: 7,371

Re: Proc SQL Create Table question

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,668

Re: Proc SQL Create Table question

[ Edited ]

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;
Occasional Contributor
Posts: 5

Re: Proc SQL Create Table question

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.

Super User
Super User
Posts: 7,668

Re: Proc SQL Create Table question

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 125 views
  • 1 like
  • 5 in conversation