Macro & SQL Update.

[dhavalyparikh]

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

[SASKiwi · Accepted solution]

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;

[dhavalyparikh]

Thanks. That works!

[Kurt_Bremser]

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.

[Ksharp]

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


%put _all_;

%put &=SYSUSERID ;

[Tom]

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;