Can anyone explain this with example?
I am getting an error when defining composite primary key through proc sql create table statement. Always a problem with syntax but I cannot figure out what is the problem..
PRIMARY KEY (column-1 <, column-2, ...>)
specifies one or more primary key columns, that is, columns that do not contain missing values and whose values are unique.
Restriction | When you are defining overlapping primary key and foreign key constraints, the variables in a data file are part of both a primary key and a foreign key definition. If you use the exact same variables, then the variables must be defined in a different order. |
The code you're using and log with errors would be helpful here.
This is the code I am executing.
The primary key should have claim_id,patient_id,Seq_no) but it is not executing
109 proc sql;
110 create table swar_am.diagnosis
111 (Claim_id char(6) references 'claims',
112 Patient_id char(6) references 'patient',
113 Seq_no char(6),
114 code_set char(50),
115 code char(20),
116 primary key(Seq_no,Claim_id,Patient_id);
---
22
76
ERROR 22-322: Syntax error, expecting one of the following: CHAR, CHARACTER, DATE, DEC, DECIMAL,
DOUBLE, FLOAT, INT, INTEGER, NUM, NUMERIC, REAL, SMALLINT, VARCHAR.
ERROR 76-322: Syntax error, statement will be ignored.
This is another error.
142 proc sql;
143 create table swar_am.claims
144 (Patient_id char(6) references 'patient',
145 Claim_id char(6) ,
146 claim_type char(10),
147 dt_admission num format = mmddyy10.,
148 dt_discharge num format = mmddyy10.,
149 dt_service num format = mmddyy10.,
150 val_pmt_total num format= Dollar6.3)
151 ;
ERROR: Creation of integrity constraint _FK0001_ failed because half of the referential integrity
constraint would reside within a temporary library. WORK is a temporary library.
Here claim_id and patient_id should be primary key..
Thank you. it worked
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.