topic Re: Insert and update using proc sql simultaneously in SAS Programming

Insert and update using proc sql simultaneously

india2016 — Fri, 28 Jul 2017 08:13:56 GMT

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;

Re: Insert and update using proc sql simultaneously

Kurt_Bremser — Fri, 28 Jul 2017 08:41:09 GMT

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.

Re: Insert and update using proc sql simultaneously

india2016 — Fri, 28 Jul 2017 09:05:45 GMT

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

Re: Insert and update using proc sql simultaneously

india2016 — Fri, 28 Jul 2017 11:22:34 GMT

@Kurt_Bremser : And i have to write proc only for this.

Re: Insert and update using proc sql simultaneously

Kurt_Bremser — Fri, 28 Jul 2017 11:25:46 GMT

@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;

Re: Insert and update using proc sql simultaneously

india2016 — Fri, 28 Jul 2017 12:12:51 GMT

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.

Re: Insert and update using proc sql simultaneously

Kurt_Bremser — Fri, 28 Jul 2017 12:18:50 GMT

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;

Re: Insert and update using proc sql simultaneously

india2016 — Fri, 28 Jul 2017 12:36:24 GMT

Thanks Kurt.

Re: Insert and update using proc sql simultaneously

india2016 — Fri, 28 Jul 2017 12:37:39 GMT

KurtBremser: Thanks Kurt it worked.

Re: Insert and update using proc sql simultaneously

india2016 — Fri, 28 Jul 2017 12:48:11 GMT

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.

Re: Insert and update using proc sql simultaneously

Kurt_Bremser — Fri, 28 Jul 2017 12:54:47 GMT

@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.