Update SQL Table from CSV file

Reply
New Contributor
Posts: 2

Update SQL Table from CSV file

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;

 

Super User
Posts: 23,296

Re: Update SQL Table from CSV file

[ Edited ]

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;

 


 

New Contributor
Posts: 2

Re: Update SQL Table from CSV file

[ Edited ]

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;

 

Super User
Posts: 23,296

Re: Update SQL Table from CSV file

Read the docs Smiley Happy

 

http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n1spf244gkq54cn13bbiy307hknr.htm&docsetV...

 

/*-------------------------------------------------------------------
   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. 

 

 

Super Contributor
Posts: 276

Re: Update SQL Table from CSV file

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.

OR

- 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

 

Super User
Super User
Posts: 7,934

Re: Update SQL Table from CSV file

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;
Super User
Posts: 9,890

Re: Update SQL Table from CSV file

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 6 replies
  • 297 views
  • 0 likes
  • 5 in conversation