Help using Base SAS procedures

proc sql insert a row

Accepted Solution Solved
Reply
Regular Contributor
Posts: 154
Accepted Solution

proc sql insert a row

Hi, I have a question in proc sql procedure how to insert a row.

 

Initial table (Table_1) looks like this:

 

Log_Id    |    Start_Time                                  |  End_Time

-----------------------------------------------------------------------------

   29        |     16DEC2017:23:46:59.733390  |  

-----------------------------------------------------------------------------

   30        |     16DEC2017:23:47:23.833490  |             

 

 

I want to insert a row where 'Log_Id' is a sequential number to prior ones (31 in this example); and 'Start_Time' is current time. Leave 'End_Time' blank. 

 

My code: 

proc sql;
		insert into Table_1
				set Log_id = 31
				, Start_Time = datetime()
                                , End_Time = 
					; 
	quit;

1) How to make 'Log_Id' a sequential number to prior rows?

2) It has error if I leave 'End_Time' blank. And that column is of datetime format. 

 

Thank you so much.


Accepted Solutions
Solution
‎12-17-2017 08:24 PM
PROC Star
Posts: 260

Re: proc sql insert a row

I think this will work:

proc sql noprint;
  select 
    max(Log_id)+1 into :Log_id
  from Table_1;
  insert into Table_1
    set Log_id=&Log_id,
        Start_Time=datetime();
quit;

You could also do it in one step, but then you would have to use UNDOPOLICY=NONE, as you are inserting into the same table that you are querying for the MAX value. It is not necessary to supply a value for End_Date, it will automatically be set missing.

View solution in original post


All Replies
Super User
Posts: 10,211

Re: proc sql insert a row

I'd do it automatically with a data step:

data table_1;
set table_1 end=done;
output;
if done
then do;
  log_id + 1;
  start_time = datetime();
  end_time = .;
  output;
end;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 154

Re: proc sql insert a row

Posted in reply to KurtBremser
Thanks!
Super User
Posts: 5,876

Re: proc sql insert a row

1) What I think you are looking for an automatic constraint. Unfortunately it's not supported by SAS engines. You need to look up the existing max value first.
2) If you want to assign a NULL/MISSING value to a numerical variable, use a dot (as seen in the data step by @KurtBremser). You must always place something to the right of the equal sign in SAS syntax.
Data never sleeps
Regular Contributor
Posts: 154

Re: proc sql insert a row

Thank you and that is the point.

Solution
‎12-17-2017 08:24 PM
PROC Star
Posts: 260

Re: proc sql insert a row

I think this will work:

proc sql noprint;
  select 
    max(Log_id)+1 into :Log_id
  from Table_1;
  insert into Table_1
    set Log_id=&Log_id,
        Start_Time=datetime();
quit;

You could also do it in one step, but then you would have to use UNDOPOLICY=NONE, as you are inserting into the same table that you are querying for the MAX value. It is not necessary to supply a value for End_Date, it will automatically be set missing.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 347 views
  • 1 like
  • 4 in conversation