Good morning everyone, I’m having a little trouble with what should be a simple PROC SQL CREATE TABLE in SAS EG. I imported an excel document into SAS EG and used a CREATE TABLE statement to make it into a table in SAS EG called TABLE2. I’m now trying to join it with a table in DB2, but am getting the error: work.table2 is an undefined name. SQLSTATE=42704. This is my code: ——— %loginmacro; proc sql; CONNECT TO db2(login stuff); CREATE TABLE table3 as SELECT * from connection to db2( SELECT A.*, B.date FROM work.table2 a INNER JOIN schema.db2table b ON a.ID = b.ID ) ; Disconnect from db2; Quit; ———— So table2 definitely exists because I did another program that simply was CREATE TABLE test as SELECT * from work.table2, just to make sure I wasn’t crazy, and it worked. But the above code gives me the undefined error. Is there something in the ordering I am doing wrong? Thank you! ... View more

I have a run I inherited that transposes data. For some reason, this transpose sticks 30+ empty spaces in front of the NOV2018 values, so the requester has to trim it and has asked if I can prevent that issue. I inserted a Query Builder step to trim, but noticed that the number of columns generated by the run depends on the data. So the columns I grab with the query builder are not always there, causing the run to fail. I’m looking into using PROC SQL language to achieve this instead of QB, but I am running into a similar issue. I need to use SELECT * to make sure I grab each column that’s created, but I can’t trim down the date column without naming it in the SELECT statement. If I name the columns, I run into the same problem as QB. Is there a way to SELECT * in QB or PROC SQL and still be able to trim a named column? ... View more

Hi folks! I have a run that returns a column in datetime format like this: 01SEP2021 01AUG2021 01JUL2021 I’m trying to write a dynamic WHERE statement in Proc SQL that will only return the last month’s results. What I am trying is this: SELECT * FROM table WHERE date_column = intnx(‘month’, today(), -1, ‘same’); It’s not giving me an error, but it is returning no results. It is currently October, so I was expecting it to return only the rows with 01SEP2021 as a value (and I am certain the results have SEP values). I am not sure where I am going wrong here and appreciate any advice. Thank you! ... View more