topic Update SQL Table from CSV file in SAS Programming

Update SQL Table from CSV file

Benzula — Mon, 27 Nov 2017 20:44:43 GMT

Hello!

So I have an issue, I want to be able to update a SQL Server with a CSV file that I have imported into SAS and I think my Syntax is just wrong or I'm missing a step.

Step 1: I went to File Import and found the file for it to connect to and brought the data in

Step 2: Take the Data from said CSV and drop the table and replace it with the new information.

below is the structure I have. I am pretty sure the "Data Imported From FILENAME_OF_CSV GOES HERE" is the issue I just can't see to figure how to do it. I am not sure if I need to use the Merge function or what.

PROC SQL;

connect to odbc(dsn="MY_SERVER_NAME_GOES_HERE");

Select * From DATABASE.SCHEMA.TABLE GOES HERE

execute (

select *

into DATABASE.SCHEMA.TABLE GOES HERE

from Data Imported From FILENAME_OF_CSV GOES HERE

) by odbc;

disconnect from odbc;

QUIT;

Re: Update SQL Table from CSV file

Reeza — Mon, 27 Nov 2017 20:55:22 GMT

Pass thru SQL cannot access data imported into SAS - it passes commands directly to the server so all the data required must live on the server already.

You need to use implicit pass through or a different method to load your data.

@Benzula wrote:

Hello!

So I have an issue, I want to be able to update a SQL Server with a CSV file that I have imported into SAS and I think my Syntax is just wrong or I'm missing a step.

Step 1: I went to File Import and found the file for it to connect to and brought the data in

Step 2: Take the Data from said CSV and drop the table and replace it with the new information.

below is the structure I have. I am pretty sure the "Data Imported From FILENAME_OF_CSV GOES HERE" is the issue I just can't see to figure how to do it. I am not sure if I need to use the Merge function or what.

PROC SQL;

connect to odbc(dsn="MY_SERVER_NAME_GOES_HERE");

Select * From DATABASE.SCHEMA.TABLE GOES HERE

execute (

select *

into DATABASE.SCHEMA.TABLE GOES HERE

from Data Imported From FILENAME_OF_CSV GOES HERE

) by odbc;

disconnect from odbc;

QUIT;

Re: Update SQL Table from CSV file

Benzula — Mon, 27 Nov 2017 21:06:53 GMT

So if my file name that I imported was Master_Table would the below be the right way to do it implicitly? The Step before this function says "Data imported from Master_Table.csv"

And my database was DB1

Schemea DBO

Table Name = Table1

proc sql;

select *

into DB1.DBO.Table1

from Master_Table

quit;

Re: Update SQL Table from CSV file

Reeza — Mon, 27 Nov 2017 21:08:41 GMT

Read the docs 🙂

http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n1spf244gkq54cn13bbiy307hknr.htm&docsetVersion=9.4&locale=en#n03g3i0wk3qqyyn167u9zq91z3ov

/*-------------------------------------------------------------------
   Output 4.4 Rows Inserted with a Query

-------------------------------------------------------------------*/
proc sql;
   create table sql.newcountries
      like sql.countries;

proc sql;
   title "World's Largest Countries";
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;

   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

You would use an INSERT INTO to add your data.

Another, possibly easier to code option may be a PROC APPEND, if the data is assigned via a libname.

Re: Update SQL Table from CSV file

AhmedAl_Attar — Mon, 27 Nov 2017 21:09:34 GMT

Hi,

I'm not sure of your SAS skills level, therefore I would recommend you read some of the SAS online help in order to avoid us from saying/recommending an approach beyond your programming knowledge!

IF you have Microsoft SQL Server, you can access it via SAS/ACCESS Interface to Microsoft SQL Server or SAS/ACCESS Interface to ODBC . It all depends on which SAS/ACCESS product your organization have licensed.

Depending on how you establish connection to your SQL Server tables, be it via

- Libname Statement ---> Data Step & Proc data manipulation techniques. Including Proc SQL.

- SQL Pass-Through Facility ---> Only Proc SQL techniques.

If the data in the CSV file is considered

- New: --> Insert operation

- Update: --> Update/Modify operation

- Mixed (New & Update): --> Update/Modify operation

Look for the corresponding syntax that matches your desired operation within your approach (SQL / Data Step)

Thanks,

Ahmed

Re: Update SQL Table from CSV file

Tom — Mon, 27 Nov 2017 21:12:36 GMT

So you used PROC IMPORT to create a SAS dataset out of the CSV file and you now want to append those observations to can existing table in SQL Server?

Create a libref that points to your database/schema and then use PROC APPEND to add the records.

libname x odbc ...... ;
proc append base=x.tablename data=dsname ;
run;

Re: Update SQL Table from CSV file

Kurt_Bremser — Tue, 28 Nov 2017 07:33:31 GMT

MS SQL server is perfectly capable of importing the csv on its own, so why do the unnecessary detour through SAS?

https://stackoverflow.com/questions/15242757/import-csv-file-into-sql-server