DATA Step, Macro, Functions and more

base sas to proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

base sas to proc sql

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


Accepted Solutions
Solution
‎04-10-2018 05:37 AM
Super User
Super User
Posts: 9,799

Re: base sas to proc sql

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 10,530

Re: base sas to proc sql


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 76

Re: base sas to proc sql

Posted in reply to KurtBremser

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

Solution
‎04-10-2018 05:37 AM
Super User
Super User
Posts: 9,799

Re: base sas to proc sql

[ Edited ]

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;
Super User
Posts: 10,530

Re: base sas to proc sql

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,886

Re: base sas to proc sql

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 118 views
  • 0 likes
  • 4 in conversation