topic Re: Macro & SQL Update. in SAS Programming

Macro & SQL Update.

dhavalyparikh — Wed, 17 Aug 2022 21:44:59 GMT

%macro Update_table(a, id, c) ;

prc sql noprint ;

select id into :id from libnew.table1 where program="&a"

select value into :username from SASHelp.vmacro where name = 'sysuserid'

run;

%put &username;

Proc SQL;

update table1

set Username = &username

where id = &id.;

run;

%mend Update_table;

The issue is I want to use :username result value with my update. I just want to know what's the best way to use :username variable in the update so I can update the value stored in the username variable.

Re: Macro & SQL Update.

SASKiwi — Thu, 18 Aug 2022 01:15:17 GMT

Given the variables you are trying to update are character then they need to be enclosed in double quotes to resolve correctly:

%macro Update_table(a, id, c) ;

prc sql noprint ;
select id into :id  from libnew.table1 where program="&a"
select value into :username from SASHelp.vmacro where name = 'sysuserid'
run;

%put &username;


Proc SQL;
update table1
set Username = "&username"
where id = "&id.";
run;

%mend Update_table;

Re: Macro & SQL Update.

Kurt_Bremser — Thu, 18 Aug 2022 04:44:24 GMT

Why so complicated?

select value into :username from SASHelp.vmacro where name = 'sysuserid'

is equivalent to

%let username = &sysuserid.;

But you can directly use SYSUSERID in your SQL:

proc SQL;
update table1
set Username = "&sysuserid."
where id = &id.;
quit;

SQL statements are executed immediately, so no RUN is needed, but a QUIT to end the procedure.

Re: Macro & SQL Update.

Ksharp — Thu, 18 Aug 2022 12:21:23 GMT

You could use the following to check the list of all the system macro variable, And refer it directly :

%put _all_;

%put &=SYSUSERID ;

Re: Macro & SQL Update.

Tom — Thu, 18 Aug 2022 13:31:54 GMT

If you want the value of SYSUSERID then just reference it directly. No need to run any queries.

proc SQL;
update table1
  set Username = "&sysuserid"
  where id in (select id from libnew.table1 where program="&a")
;
quit;

But you could also just use the SQL keyword USER. That will have the same value as SYSUSERID.

proc SQL;
update table1
  set Username = USER
  where id in (select id from libnew.table1 where program="&a")
;
quit;

Re: Macro & SQL Update.

dhavalyparikh — Thu, 18 Aug 2022 15:54:45 GMT

Thanks. That works!