BookmarkSubscribeRSS Feed
Soha
Calcite | Level 5
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
8 REPLIES 8
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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.
Doc_Duke
Rhodochrosite | Level 12
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.
deleted_user
Not applicable
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]
Swade016
Calcite | Level 5
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;
deleted_user
Not applicable
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5063 views
  • 1 like
  • 5 in conversation