topic Re: base sas to proc sql in SAS Programming

base sas to proc sql

sathya66 — Tue, 10 Apr 2018 08:19:44 GMT

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,

Re: base sas to proc sql

Kurt_Bremser — Tue, 10 Apr 2018 08:31:15 GMT

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

Re: base sas to proc sql

sathya66 — Tue, 10 Apr 2018 08:52:11 GMT

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,

Re: base sas to proc sql

RW9 — Tue, 10 Apr 2018 09:03:27 GMT

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;

Re: base sas to proc sql

Kurt_Bremser — Tue, 10 Apr 2018 09:15:02 GMT

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.

Re: base sas to proc sql

ballardw — Tue, 10 Apr 2018 16:48:50 GMT

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.