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;
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;
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;
Read the docs 🙂
/*-------------------------------------------------------------------
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.
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.