DATA Step, Macro, Functions and more

Insert and update using proc sql simultaneously

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Insert and update using proc sql simultaneously

I am importing a file using proc import

proc import datafile="/opt/Export_d.csv" replace
out=work.export_data; run;

THEN I SELECTED 2 VARIBLES FROM EXPORTED TABLE HAVING SOME CONDITION

proc sql;
create table work.login2
as select ' SURVEY_ID'n, TIME format=datetime20.,' USERNAME'n
from work.export_data
having TIME=max(TIME) and ' USERNAME'="sasdemo";
quit;

then i created a macro for survey_id because i have to update a value with the Survey_id reference in different table shown in below code:

proc sql;
select max(' SURVEY_ID'n) into: sid from work.login2;
quit;

now if the ID is blank then i dont have to insert anything but if we have survey_id we have update few columns in a table using below code which is not happening.


proc sql;
case when ' SURVEY_ID'n is not null then update table_name set SAS_TRACKING_CODE="&Trackingcode."
where SURVEY_ID=&sid.
else end;
quit;


Accepted Solutions
Solution
‎07-28-2017 08:46 AM
Super User
Posts: 7,782

Re: Insert and update using proc sql simultaneously

Posted in reply to india2016

Then run two updates:

proc sql;
update table_name
set SAS_TRACKING_CODE = "&Trackingcode."
where SURVEY_ID=&sid. and SURVEY_ID is not null;
update table_name
set SAS_TRACKING_CODE = "&another_Trackingcode."
where SURVEY_ID is null;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,782

Re: Insert and update using proc sql simultaneously

[ Edited ]
Posted in reply to india2016

Use a data step:

/* let's get rid of those stupid column names containing blanks in the first step */
proc import
  datafile="/opt/Export_d.csv"
  out=work.export_data (rename=(' SURVEY_ID'n=survey_id ' USERNAME'n=username))
  replace
;
run;

proc sql;
create table work.login2 as
select
  survey_id,
  TIME format=datetime20.,
  username
from work.export_data
where username="sasdemo"
having TIME=max(TIME)
;
quit;

proc sql;
select max(survey_id) into: sid from work.login2;
quit;

/* use a data step to update, much simpler code */
data table_name;
set table_name;
if survey_id ne . and survey_id = &sid
then SAS_TRACKING_CODE = "&Trackingcode.";
run;

Edit: changed the first SQL to have a where and having clause.


---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: Insert and update using proc sql simultaneously

Posted in reply to KurtBremser
I can not create a table because i don't have access. I just have insert and update access of that table.
Super User
Posts: 7,782

Re: Insert and update using proc sql simultaneously

Posted in reply to india2016

india2016 wrote:
I can not create a table because i don't have access. I just have insert and update access of that table.

Then your update should look like that:

proc sql;
update table_name
set SAS_TRACKING_CODE = "&Trackingcode."
where SURVEY_ID=&sid. and SURVEY_ID is not null;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: Insert and update using proc sql simultaneously

Posted in reply to KurtBremser
KurtBremser: Thanks for your prompt reply but i have done this. I need to add one more condition where Survey_ID is null then I have to insert something else.
Solution
‎07-28-2017 08:46 AM
Super User
Posts: 7,782

Re: Insert and update using proc sql simultaneously

Posted in reply to india2016

Then run two updates:

proc sql;
update table_name
set SAS_TRACKING_CODE = "&Trackingcode."
where SURVEY_ID=&sid. and SURVEY_ID is not null;
update table_name
set SAS_TRACKING_CODE = "&another_Trackingcode."
where SURVEY_ID is null;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: Insert and update using proc sql simultaneously

Posted in reply to KurtBremser
Thanks Kurt.
Contributor
Posts: 60

Re: Insert and update using proc sql simultaneously

Posted in reply to india2016
KurtBremser: I have one more doubt what if I dont want to do anything if the Survey_ID is null. Simply execute the proc. Will it work if I remove the 2nd update statment.
Super User
Posts: 7,782

Re: Insert and update using proc sql simultaneously

Posted in reply to india2016

india2016 wrote:
KurtBremser: I have one more doubt what if I dont want to do anything if the Survey_ID is null. Simply execute the proc. Will it work if I remove the 2nd update statment.

Yes. The

and SURVEY_ID is not null

will prevent that.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: Insert and update using proc sql simultaneously

Posted in reply to KurtBremser
KurtBremser: Thanks Kurt it worked.
Contributor
Posts: 60

Re: Insert and update using proc sql simultaneously

Posted in reply to KurtBremser
@KurtBremser : And i have to write proc only for this.
☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 167 views
  • 0 likes
  • 2 in conversation