Retrieve Dataset from SQL Server

[Kumar6]

Hii,

I have a created an ADSL dataset with multiple studies and I loaded this dataset in to a database to build a data repository.

Now,  I need to retrieve this ADSL dataset from Database to my study folder where studyid = 6300.

From Data Management team I got below command and said to execute the following procedure in SAS proc sql:

EXEC bst.usp_ADS_SHOW_DATASET  '6300', 'ADSL'.

I didn't understand how to use mentioned command to retrieve the dataset. Can anyone suggest me a solution for this.

FYI:

I have tried below code but it didn't worked to me.

libname p2_ads oledb init_string = "Provider = SQLOLEDB.1;
BULKLOAD = YES;
Integrated Security = SSPI;
Persist Security Info = True;
Initial Catalog = VCDM_ANALYxxTIC;
Data Source = ITSUSRAxxxxx,1000"
Schema = BST;
;

proc sql;
create table adsl33 as
select *
from EXEC p2_ads.usp_ADS_SHOW_DATASET, "6300", "ADDX"
;
quit;

LOG:

150 proc sql;
151 create table adsl33 as
152 select *
153 from EXEC p2_ads.usp_ADS_SHOW_DATASET, "6300", "ADDX"
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT,
FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER,
RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

154 ;
155 quit

Thank You for your time,

Santhosh.

[r_behata]

Hi @Kumar6 

You need an SQL Pass through  query to execute a SQL server Stored Process. 

Syntax looks like this :

PROC SQL;
	Connect to odbc (datasrc=<data source name> user = <user name> password = <pwd>);
		Execute(p2_ads.usp_ADS_SHOW_DATASET, "6300", "ADDX") by odbc;
	Disconnect from odbc;
QUIT;

[Kumar6]

Hi r_behata,

What I have to provide for user and password.
for datasrc I have given ITSUSRAWSP02788,1433.

I tried to run below code and I got a pop-up box of Data Link Properties, requested to enter server name, info to log onto the server and select database on server.

libname p2_ads oledb init_string = "Provider = SQLOLEDB.1;
BULKLOAD = YES;
Integrated Security = SSPI;
Persist Security Info = True;
Initial Catalog = VCDM_ANALYTIC;
Data Source = ITSUSRAWSP02788,1433"
Schema = BST;
;

PROC SQL;
Connect to oledb (datasrc=ITSUSRAWSP02788,1433 user = skumar password =);
Execute(p2_ads.usp_ADS_SHOW_DATASET, "6300", "ADSL") by oledb;
Disconnect from oledb;
QUIT;

Also in p2_ads library I don't see a dataset with usp_ads_show_dataset.
Once again I want to provide the command I got from DM team is
EXEC bst.usp_ADS_SHOW_DATASET '6300', 'ADSL'

In my program I'm using P2_ADS instead of BST. Is that okay ?

[Patrick]

You probably need to take a step-by-step approach to make this work.

Step 1: Connectivity to the database.

You wrote that you've loaded data into a database table. Did you use SAS for this? If yes then make sure that you're using the exact same libname/libref now.

If not: Try and see if there is already a SAS library defined for you (i.e. for SAS EG/Studio under the Server). If there is one activate the library (assign) and drill into it. Or: Is there someone else using SAS to connect to this database? If yes then get this already working libname statement from this person.

Step 2: List table metadata

IF you've got a library defined then issue below statement (let's assume the libref is called mylib). Does this work? And in the report created do you see the table name you're after?

proc contents data=mylib._all_ short;
run;

Let's discuss step 3 once above is working. It should be simple and if you just need data read into SAS will likely not require a stored procedure.

[SASKiwi · Accepted solution]

To execute an SQL Server stored procedure you will probably need to do something similar to this:

PROC SQL;
Connect to oledb (datasrc=ITSUSRAWSP02788,1433 user = skumar password =);
Execute(EXEC bst.usp_ADS_SHOW_DATASET '6300', 'ADSL';) by oledb;
Disconnect from oledb;
QUIT;

Please note you have to include the EXEC inside the brackets to tell SQL Server to run a stored procedure. Also note you can't add a SELECT statement in front of the SAS EXECUTE to read data. That has to be done in a separate SQL statement.

[Kumar6]

Hi saskiwi,

As per your comments I tried below code by adding select statement in a separate SQL statement. The log says that library reference bst is not assigned.
Also I tried to run the program without lib reference(bst) then log says usp_ads_show_dataset is not exists.

When I run the program without keeping separate SQL statement(which are line no: 13-15) program went through without errors but where can I see the retrieved data without giving create table statement ?

libname tst "C:\Users\SDosapat\Desktop\e_tst";

1.PROC SQL;
2.Connect to oledb (init_string = "Provider = SQLOLEDB.1;
3. BULKLOAD = YES;
4. Integrated Security = SSPI;
5. Persist Security Info = True;
6. Initial Catalog = VCDM_ANALYTIC;
7. Data Source = ITSUSRAWSP02788,1433"
8. Schema = BST );
9.
10.
11.Execute(EXEC bst.usp_ADS_SHOW_DATASET '6300', 'ADSL';) by oledb;
12.
13.Create table tst.adsl3 as
14.select *
15.from bst.usp_ADS_SHOW_DATASET;
16.
17.Disconnect from oledb;
18.quit;

Thanks for your time.

Regards,
Santhosh.

[SASKiwi]

@Kumar6  You need to use SQL passthru for your SELECT statement:

Create table tst.adsl3 as
select *
from connection to oledb
(select * from
bst.usp_ADS_SHOW_DATASET
);

[Kumar6]

Hi Saskiwi,
The below code worked for me to extract ADSL from DB for study 6300.
Is there any way to extract multiple studies in single time like in("xxxx" "xxxx" "xxxx" ).

proc sql;
Connect to oledb (init_string = "Provider = SQLOLEDB.1;
BULKLOAD = YES;
Integrated Security = SSPI;
Persist Security Info = True;
Initial Catalog = VCDM_ANALYTIC;
Data Source = ITSUSRAWSP02788,1433"
Schema = BST );

Create table extract.adsl_6300 as
select *
from connection to oledb
( EXEC bst.usp_ADS_SHOW_DATASET "6300", "ADSL"
);
quit;

Thank you,
Kumar.