Create a PostgreSQL table from a SAS table


Keywords:postgresql 


Question: 

I am creating a PostgreSQL table from a sas table and the sas table is showing below.type of column woe is numeric and other column are type of char.

+----------+--------------+------+-------+-----+-----+
| variable | new_variable | type | start | end | woe |
+----------+--------------+------+-------+-----+-----+
| A        | mi_A         | char | 1     |     | 1.3 |
| A        | mi_A         | char | 0     |     | 0.6 |
| B        | mi_B         | char | 1     |     | 5.4 |
| B        | mi_B         | char | 0     |     | 0.1 |
| gnd_cd   | gnd_cd       | char | 3     |     | 1.3 |
| gnd_cd   | gnd_cd       | char | @0    |     | 0.6 |
| gnd_cd   | gnd_cd       | char | 2     |     | 5.4 |
| gnd_cd   | gnd_cd       | char | N     |     | 0.1 |
| gnd_cd   | gnd_cd       | char | 1     |     | 1.3 |
| gnd_cd   | gnd_cd       | char | 99    |     | 0.6 |
| mar_sign | mar_sign     | char | 0     |     | 5.4 |
| mar_sign | mar_sign     | char | Y     |     | 0.1 |
| mar_sign | mar_sign     | char | N     |     |   6 |
+----------+--------------+------+-------+-----+-----+

the client shows an error:syntax error at or near "end".I think the error may possibly caused by the "start" column.still don't why and how to fix it. my code is simply a sql,and tableA is for postgresql and tableB is from SAS:

create table schema.tableA from select * from mywork.tableB;

any advice are appreciated!


1 Answer: 

SAS is much more flexible in allowing keywords for variable names. In your postgresql code you might need to add double quotes around variable or dataset names to prevent it from thinking you are typing a keyword. So use

select "end" from ... 

If you do then watch out for case of the names. Without quotes Postgres will default to creating/searching for name in all lowercase. But once you add the quotes then the value in the quotes must match exactly the variable name. So if you create variable named Name it will actually create a variable named name and you can reference as NAME or NaMe.

But if you create a variable in mixed case using quote, like "Name", then the uppercase N will be in the variable name and you must use "Name" to reference it. Name without the quotes will not work since it will look for variable with lowercase, name.