BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sathya66
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
Kurt_Bremser
Super User

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

sathya66
Barite | Level 11

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Kurt_Bremser
Super User

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.

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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