%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.
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;
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;
Thanks. That works!
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.