topic Re: Proc SQL Create Table question in SAS Programming

Proc SQL Create Table question

acorey25 — Mon, 10 Jul 2017 14:07:53 GMT

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: The following columns were not found in the contributing tables: CLR_Recip_Parish, LPAR_Parish_Desc.

I appreciate any and all feedback. Thanks!

Re: Proc SQL Create Table question

ballardw — Mon, 10 Jul 2017 14:17:36 GMT

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.

Re: Proc SQL Create Table question

Kurt_Bremser — Mon, 10 Jul 2017 14:21:42 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Proc SQL Create Table question

Shmuel — Mon, 10 Jul 2017 14:30:07 GMT

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

Re: Proc SQL Create Table question

RW9 — Mon, 10 Jul 2017 14:31:04 GMT

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;

Re: Proc SQL Create Table question

acorey25 — Fri, 14 Jul 2017 14:53:34 GMT

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

_______________________

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;

_______________________

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.

Re: Proc SQL Create Table question

RW9 — Fri, 14 Jul 2017 15:48:00 GMT

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