Hi All,
I want to write base sas code to proc sql.
data test;
merge test (in=a) name (in=b);
by id;
if a=b then do;
%all(test);
end;
if a then output;
run;
Thanks,
SS
So what is it your actually trying to achieve with this code? Follow the guidance on posting new questions: Provide test data in the form of a datastep so we have something to run, and what you want out at the end. It appears to me that you have a convoluted process to fill some missing data in with defaults. You do not need any of this, you could just merge and use arrays for example.
In SQL, you can do:
proc sql; create table want as select a.var1,
a.var2,
b.var3,
case when coalesce(a.id,b.empid)=" " then "xxxx" else coalesce(a.id,b.empid) end as id
from test a
left join name b
on a.id=b.empid;
quit;
Of course that is just a code snippet and there are lots of other bits, question is is it worth it as you wont get anthing different to the code you already have, and if you update then its far simpler. Also note that using proc sql will not solve the sort, as it just does the sort internally.
data test;
merge test (in=a) name (in=b);
by id;
array n _numeric_;
array c _character_;
if a=b then do;
do over n;
if n=. then n=1111;
end;
do over c;
if c=" " then c="xxxx";
end;
end;
if a then output;
run;
@sathya66 wrote:
Hi All,
I want to write base sas code to proc sql.
data test; merge test (in=a) name (in=b); by id; if a=b then do; %all(test); end; if a then output; run;
Thanks,
SS
Since your macro contains data step code, this will not be possible.
You will also have to rewrite your macro; to see if your macro code can be converted to SQL, we will need to see it.
If your data step code works, there is no reason to convert it to SQL.
Thanks Kurt,
My base sas code is working fine but I want to make it dynamic and I don't want to sort the data because by variable name is different in other tables (ex: ID, in other table this ID is "empID") so I am looking in proc sql.
my all macro
%macro test(dsname,varname);
%if %varexist(&dsname,&varname) %then %do;
%if %getvartype(&dsname,&varname)=C %then %do;
if &varname ne ' ' then
&varname = "xxxxx";
%end;
%else %if %getvartype(&dsname,&varname)=N %then %do;
if &varname ne . then
&varname =11111;
%end;
%end;
%mend test;
%macro all(dsname);
%test(&dsname,Name);
%test(&dsname,forename);
%test(&dsname,sal);
%mend all;
Thanks,
SS
So what is it your actually trying to achieve with this code? Follow the guidance on posting new questions: Provide test data in the form of a datastep so we have something to run, and what you want out at the end. It appears to me that you have a convoluted process to fill some missing data in with defaults. You do not need any of this, you could just merge and use arrays for example.
In SQL, you can do:
proc sql; create table want as select a.var1,
a.var2,
b.var3,
case when coalesce(a.id,b.empid)=" " then "xxxx" else coalesce(a.id,b.empid) end as id
from test a
left join name b
on a.id=b.empid;
quit;
Of course that is just a code snippet and there are lots of other bits, question is is it worth it as you wont get anthing different to the code you already have, and if you update then its far simpler. Also note that using proc sql will not solve the sort, as it just does the sort internally.
data test;
merge test (in=a) name (in=b);
by id;
array n _numeric_;
array c _character_;
if a=b then do;
do over n;
if n=. then n=1111;
end;
do over c;
if c=" " then c="xxxx";
end;
end;
if a then output;
run;
You will have to create a SQL step first that does what you want for a single dataset. Once that works, you can start to make the code dynamic.
Changing only one or several variables while leaving the others untouched is done with the update statement:
proc sql;
create table class as select * from sashelp.class;
update class set name = 'XXXX', age = 1111;
quit;
You will need to create a SQL-suitable version of your macro that inserts the wanted variables into the set part.
Note that a data step merge does things with variables common to both sets depending on the order they appear on the merge statement that you will have to explicitly code in sql for every single variable to get the same result from proc sql.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.