- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 04-24-2008 08:36 PM
(6545 views)
I have written the following stored procedure in SQL and I wnat the same thing to be done in SAS. I tried to use Proc SQL but I dont think it has conceot of cursors. So, please can anyone convert this into a SAS program?
The program is trying to do the following:
I had a variable called SCH_DEP_TIME which has numeric value in Mins and this variable also has some missing values. I want to impute the missing values in that column. So, I first categorised the SCH_DEP_TIME variable into 0,1 and 2( 0 if the time falls from 6 am to 6pm, 1 if the time falls from 6pm to 6AM and 2 if there is a missing value. The new variable name is SCH_DEP_TIME_CAT).
I have another two variables called ORIGIN and FL_NUM. A flight with a particular FL_NUM from a particular ORIGIN starts only at one caegory of time ( either 1 or 0, i.e either between 6 am to 6pm or between 6 pm to 6 am). Now if there is a FL_NUM (say FL_NUM = 1) which starts from a particular origin(say ORIGIN = 1) on 20 differesnt dates and on 15 days it has the SCH_DEP_TIME_CAT and other 5 days it has missing values in that variable. On all the 15 dates the SCH_DEP_TIME_CAT will be same. So, I want to put the value of SCH_DEP_TIME_CAT which these 15 days has into those five records which has the value of 2 in SCH_DEP_TIME_CAT (i.e these records has missing values!)
In order to achieve the following stored proedure in SQL. Can somone pelase help me code the same thing in base SAS? Its very urgent. Please help soon
PROCEDURE
[dbo].[impute]
AS
BEGIN
SET
NOCOUNT ON
DECLARE
@flight_no nvarchar(50)
DECLARE
@flight_origin nvarchar(50)
declare
my_cursor cursor for select distinct FL_NUM, ORIGIN from test where SCHE_DEP_TIME_CAT =2
open my_cursor
FETCH NEXT FROM my_cursor into @flight_no, @flight_origin
WHILE @@FETCH_STATUS = 0
begin
update
test set SCHE_DEP_TIME_CAT = (select avg(SCHE_DEP_TIME_CAT) from test where FL_NUM = @flight_no and ORIGIN = @flight_origin and SCHE_DEP_TIME_CAT !=2) where FL_NUM = @flight_no and ORIGIN = @flight_origin and SCHE_DEP_TIME_CAT =2
--print 4
FETCH
NEXT FROM my_cursor into @flight_no, @flight_origin
end
CLOSE
my_cursor
DEALLOCATE
my_cursor
END
The program is trying to do the following:
I had a variable called SCH_DEP_TIME which has numeric value in Mins and this variable also has some missing values. I want to impute the missing values in that column. So, I first categorised the SCH_DEP_TIME variable into 0,1 and 2( 0 if the time falls from 6 am to 6pm, 1 if the time falls from 6pm to 6AM and 2 if there is a missing value. The new variable name is SCH_DEP_TIME_CAT).
I have another two variables called ORIGIN and FL_NUM. A flight with a particular FL_NUM from a particular ORIGIN starts only at one caegory of time ( either 1 or 0, i.e either between 6 am to 6pm or between 6 pm to 6 am). Now if there is a FL_NUM (say FL_NUM = 1) which starts from a particular origin(say ORIGIN = 1) on 20 differesnt dates and on 15 days it has the SCH_DEP_TIME_CAT and other 5 days it has missing values in that variable. On all the 15 dates the SCH_DEP_TIME_CAT will be same. So, I want to put the value of SCH_DEP_TIME_CAT which these 15 days has into those five records which has the value of 2 in SCH_DEP_TIME_CAT (i.e these records has missing values!)
In order to achieve the following stored proedure in SQL. Can somone pelase help me code the same thing in base SAS? Its very urgent. Please help soon
PROCEDURE
[dbo].[impute]
AS
BEGIN
SET
NOCOUNT ON
DECLARE
@flight_no nvarchar(50)
DECLARE
@flight_origin nvarchar(50)
declare
my_cursor cursor for select distinct FL_NUM, ORIGIN from test where SCHE_DEP_TIME_CAT =2
open my_cursor
FETCH NEXT FROM my_cursor into @flight_no, @flight_origin
WHILE @@FETCH_STATUS = 0
begin
update
test set SCHE_DEP_TIME_CAT = (select avg(SCHE_DEP_TIME_CAT) from test where FL_NUM = @flight_no and ORIGIN = @flight_origin and SCHE_DEP_TIME_CAT !=2) where FL_NUM = @flight_no and ORIGIN = @flight_origin and SCHE_DEP_TIME_CAT =2
--print 4
FETCH
NEXT FROM my_cursor into @flight_no, @flight_origin
end
CLOSE
my_cursor
DEALLOCATE
my_cursor
END
8 REPLIES 8
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi:
Generally, speaking, an application that has visibility of the cursor on the screen, where the cursor is or what button was pushed or what record is currently loaded into some screen fields is a front-end application. That kind of front end application could, in some DBMS systems or application environments, allow you to code the kind of cursor-aware query that you show.
But, you are correct when you say that Proc SQL does not have the concept of cursors. That's because PROC SQL is not meant to be run in a "read a row, display a row on the screen, change a row, update the row" scenario. If Proc SQL does work in this kind of scenario, it is in the background, behind a front-end application. Some databases (like Microsoft Access and possibly SQL Server) allow you to build data entry forms and reporting structures and data viewing applications that are an integral part of the database definition. SAS datasets and procedures are not as tightly coupled together as Microsoft Access forms and Access tables, for example.
The kind of "stored procedure" which you describe is different from the kind of "SAS stored process" that runs in the context of the SAS Enterprise Intelligence Platform. I surmise that you want/need to code some kind of application front end to your data -- there are many ways to do this in SAS -- SAS/AF, SAS/IntrNet and htmSQL, SAS AppDev Studio, SAS Enterprise Guide and custom tasks using .NET and .COM, JDBC and Java applets in the context of the Enterprise Intelligence Platform, BI Web services, OLE-DB ... ...
For urgent requests, your best bet for help is to contact SAS Technical Support. To contact Tech Support and open a track, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem".
cynthia
Generally, speaking, an application that has visibility of the cursor on the screen, where the cursor is or what button was pushed or what record is currently loaded into some screen fields is a front-end application. That kind of front end application could, in some DBMS systems or application environments, allow you to code the kind of cursor-aware query that you show.
But, you are correct when you say that Proc SQL does not have the concept of cursors. That's because PROC SQL is not meant to be run in a "read a row, display a row on the screen, change a row, update the row" scenario. If Proc SQL does work in this kind of scenario, it is in the background, behind a front-end application. Some databases (like Microsoft Access and possibly SQL Server) allow you to build data entry forms and reporting structures and data viewing applications that are an integral part of the database definition. SAS datasets and procedures are not as tightly coupled together as Microsoft Access forms and Access tables, for example.
The kind of "stored procedure" which you describe is different from the kind of "SAS stored process" that runs in the context of the SAS Enterprise Intelligence Platform. I surmise that you want/need to code some kind of application front end to your data -- there are many ways to do this in SAS -- SAS/AF, SAS/IntrNet and htmSQL, SAS AppDev Studio, SAS Enterprise Guide and custom tasks using .NET and .COM, JDBC and Java applets in the context of the Enterprise Intelligence Platform, BI Web services, OLE-DB ... ...
For urgent requests, your best bet for help is to contact SAS Technical Support. To contact Tech Support and open a track, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem".
cynthia
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have been having a problem with accessing a MS Access database ..Here is my code. Can anyone tell me how to go by the rename procedure because I tried the rename at the end of create table as well as at the end of select * from StudentMaster but that didn't change the column name of the table.The table was created but the rename didn't work... I would appreciate any help ...This is urgent!!!!
Secondly how do you use the data step in conjunction with proc sql... is there any way to have the data step in the proc sql step ...the disconnect statement is always the last but I would need to access the database and use specific sql queries and perform statistical analysis if any
Code:
LIBNAME HOSSDB 'C:\DSL_SHARE\Master Database';
proc SQL;
Connect to ODBC (dsn=HOSS);
Create table HOSSDB.Student(rename=(HSM_StudentID=StudentID HSM_RITID=UID HSM_STDOB=DOB))
As select * from connection to ODBC(select * from StudentMaster);
disconnect from ODBC;
quit;
run;
Thanks
Ram
Secondly how do you use the data step in conjunction with proc sql... is there any way to have the data step in the proc sql step ...the disconnect statement is always the last but I would need to access the database and use specific sql queries and perform statistical analysis if any
Code:
LIBNAME HOSSDB 'C:\DSL_SHARE\Master Database';
proc SQL;
Connect to ODBC (dsn=HOSS);
Create table HOSSDB.Student(rename=(HSM_StudentID=StudentID HSM_RITID=UID HSM_STDOB=DOB))
As select * from connection to ODBC(select * from StudentMaster);
disconnect from ODBC;
quit;
run;
Thanks
Ram
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi:
For urgent requests, your best bet for help is to contact SAS Technical Support. To contact Tech Support and open a track, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem".
You may not want to use the RENAME method due to this Tech Support note:
http://support.sas.com/kb/12/352.html
Another way of creating variables with different names is to do it in the SELECT statement or to create your data set with the existing names and then use PROC DATASETS or other methods to perform the rename.
Tech Support can help you with any of these methods.
You asked whether there was any way to have the data step in the proc sql step. The short answer is NO. Generally, you do not stay connected to the database while you do your analysis on the data with SAS. Most often, you create a WORK copy of your file or a subset of the file with your records or observations of interest and then run the analysis or the DATA step progam against that WORK file or subset.
For more help with your processing tasks, especially if they are urgent, your best resource is SAS Technical Support.
cynthia
For urgent requests, your best bet for help is to contact SAS Technical Support. To contact Tech Support and open a track, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem".
You may not want to use the RENAME method due to this Tech Support note:
http://support.sas.com/kb/12/352.html
Another way of creating variables with different names is to do it in the SELECT statement or to create your data set with the existing names and then use PROC DATASETS or other methods to perform the rename.
Tech Support can help you with any of these methods.
You asked whether there was any way to have the data step in the proc sql step. The short answer is NO. Generally, you do not stay connected to the database while you do your analysis on the data with SAS. Most often, you create a WORK copy of your file or a subset of the file with your records or observations of interest and then run the analysis or the DATA step progam against that WORK file or subset.
For more help with your processing tasks, especially if they are urgent, your best resource is SAS Technical Support.
cynthia
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Cynthia,
What SOHA was meaning by a cursor is not a screen cursor, but an Oracle PL/SQL concept. The cursor is essentially a buffered iterator through a select statement, similar to a view. The term "cursor" is used to differentiate it from the formal VIEW and TABLE structures in the database.
What SOHA was meaning by a cursor is not a screen cursor, but an Oracle PL/SQL concept. The cursor is essentially a buffered iterator through a select statement, similar to a view. The term "cursor" is used to differentiate it from the formal VIEW and TABLE structures in the database.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
On using the DATA step in conjunction with PROC SQL, that is easiest to accomplish with the LIBNAME approach to connecting to the database. The you can treat the database tables nearly like SAS datasets.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I would approach this problem in one of many ways:
1) Use SAS's SQL Pass-Through feature to "execute" the Oracle stored process.
2) Use SAS's SQL Pass-Through feature to create the initial query/extract into a SAS table (dataset), then use a DATA step, with use of RETAIN variables and "IF something = . THEN something = retain_value;" to set the value.
3) Use Doc's suggestion of using a SAS libname statement to access the database table(s) and then maybe use only one DATA step to do the processing.
You will need to play around a bit, but here's a clue:
[pre]
proc sort data=indata;
by field1 field2;
run;
data outdata;
set indata;
by field1 field2;
retain this_value;
if first.field2 then this_value = value;
if value = . then value = this_value;
run;
quit;
[/pre]
1) Use SAS's SQL Pass-Through feature to "execute" the Oracle stored process.
2) Use SAS's SQL Pass-Through feature to create the initial query/extract into a SAS table (dataset), then use a DATA step, with use of RETAIN variables and "IF something = . THEN something = retain_value;" to set the value.
3) Use Doc's suggestion of using a SAS libname statement to access the database table(s) and then maybe use only one DATA step to do the processing.
You will need to play around a bit, but here's a clue:
[pre]
proc sort data=indata;
by field1 field2;
run;
data outdata;
set indata;
by field1 field2;
retain this_value;
if first.field2 then this_value = value;
if value = . then value = this_value;
run;
quit;
[/pre]
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I've done similar things by using multiple macros. This set of code basically takes each row, does some work, updates a different table, then loops thru. Hope it helps....
%Macro Export(Status=);
Proc SQL;
Create Table StatusFound as
Select *
From Inventory.Status
Where Status = "&Status";
Quit;
*Check to see if any updates needed;
%Let DSid = %sysfunc(open(StatusFound));
%Let Num = %sysfunc(attrn(&DSid, nobs));
%Let RC = %sysfunc(close(&DSid));
%If &Num = 0 %Then %Goto NoExport;
*Assign Row Numbers;
Data StatusFound;
Set StatusFound;
N = _N_;
Run;
*Create "fake" cursor;
Data StatusFound;
Set StatusFound;
By N;
If Last.N Then Call Symput("LastN", N);
Run;
Data StatusFound;
Set StatusFound;
%Do I=1 %To &LastN;
If N=&I Then Do;
Call Symput("RecorderID_&I", RecorderID);
Call Symput("Location_&I", Location);
Call Symput("Sheet_&I", SheetName);
End;
%End;
Run;
%Do I=1 %To &LastN;
*Export new data;
%ExportExcel(&&&RecorderID_&I, &&&Location_&I, &&&Sheet_&I);
*Update status Table;
Proc SQL;
Update Table Inventory.Status as S
Set LastUpdateDate = (Select Max(Date) From Inventory.Data as D Where S.RecorderID = D.RecorderID)
Where RecorderID = "&&&RecorderID_&I";
Quit;
%End;
%NoExport:
%Mend;
%Macro Export(Status=);
Proc SQL;
Create Table StatusFound as
Select *
From Inventory.Status
Where Status = "&Status";
Quit;
*Check to see if any updates needed;
%Let DSid = %sysfunc(open(StatusFound));
%Let Num = %sysfunc(attrn(&DSid, nobs));
%Let RC = %sysfunc(close(&DSid));
%If &Num = 0 %Then %Goto NoExport;
*Assign Row Numbers;
Data StatusFound;
Set StatusFound;
N = _N_;
Run;
*Create "fake" cursor;
Data StatusFound;
Set StatusFound;
By N;
If Last.N Then Call Symput("LastN", N);
Run;
Data StatusFound;
Set StatusFound;
%Do I=1 %To &LastN;
If N=&I Then Do;
Call Symput("RecorderID_&I", RecorderID);
Call Symput("Location_&I", Location);
Call Symput("Sheet_&I", SheetName);
End;
%End;
Run;
%Do I=1 %To &LastN;
*Export new data;
%ExportExcel(&&&RecorderID_&I, &&&Location_&I, &&&Sheet_&I);
*Update status Table;
Proc SQL;
Update Table Inventory.Status as S
Set LastUpdateDate = (Select Max(Date) From Inventory.Data as D Where S.RecorderID = D.RecorderID)
Where RecorderID = "&&&RecorderID_&I";
Quit;
%End;
%NoExport:
%Mend;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Swade016,
Consider this:
[pre]
%local dsid rc Record_ID Location SheetName;
%let dsid=%sysfunc(open(StatusFound,i));
%if (&dsid > 0) %then %do;
%let rc=%sysfunc(fetch(&dsid));
%do %while (&rc = 0);
%ExportExcel( &Record_ID, &Location, &SheetName );
%let rc=%sysfunc(fetch(&dsid));
%end;
%if &rc ne -1 %then %put ERROR: failed to retrieve an observation from StatusFound ;
%let rc=%sysfunc(close(&dsid));
%end;
%else %put ERROR: failed to open StatusFound ;
[/pre]
I think this would save you some steps and from having to create all those macro variables.
Consider this:
[pre]
%local dsid rc Record_ID Location SheetName;
%let dsid=%sysfunc(open(StatusFound,i));
%if (&dsid > 0) %then %do;
%let rc=%sysfunc(fetch(&dsid));
%do %while (&rc = 0);
%ExportExcel( &Record_ID, &Location, &SheetName );
%let rc=%sysfunc(fetch(&dsid));
%end;
%if &rc ne -1 %then %put ERROR: failed to retrieve an observation from StatusFound ;
%let rc=%sysfunc(close(&dsid));
%end;
%else %put ERROR: failed to open StatusFound ;
[/pre]
I think this would save you some steps and from having to create all those macro variables.