topic Re: Proc sql in SAS Programming

Proc sql

xenophil — Sun, 18 Oct 2015 11:16:32 GMT

Table A has three variable timeStamp (TS) , ID , Click with 10000 observations , table B has two var ID and category with 20 observations , B.ID is unique values of A.ID. need to add a third column 10000 observations in A, cat1 ( empty column already added ) that has B.category values where A.ID= B.ID. this code giving error. proc sql; insert into sasuser.A.cat1 select Category from sasuser.B where sasuser.A.id= sasuser.B.id; run; 22 76 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, OUTER, UNION, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored.

Re: Proc sql

LinusH — Sun, 18 Oct 2015 11:55:02 GMT

Insert is for appending data, not updating. Use UPDATE or recreate the table uaing a join (probably simpler for your situation).

Re: Proc sql

PGStats — Sun, 18 Oct 2015 19:48:12 GMT

As @LinusH pointed at, what you need is:

proc sql; 
update sasuser.A as a
set cat1 = (select Category from sasuser.B where id=a.id);
quit;

(untested)

Re: Proc sql

xenophil — Mon, 19 Oct 2015 06:05:04 GMT

Thanks , it worked.