DATA Step, Macro, Functions and more

proc sql help

Reply
Frequent Contributor
Posts: 112

proc sql help

Can someone help me to convert this code in proc sql  please? 

 

data one;
merge one two;
by variable1 variable2 ;
if sum_1=. then sum_1=0;
balance=sum_2+sum_1;
if balance > 5000 and date eq today() or date2 eq today()  ;
run;

 

thanks in advance.

Super User
Super User
Posts: 9,193

Re: proc sql help

Can I ask why exactly?  Its just your posting a few of these, can we convert SAS code into SQL code for you posts now.  There is not technical reason why you would need to convert these, unless you are passing it through to a database - in which case you should look at the database specific SQL functions.  

 

If this is purely for your preference, then perhaps its a good task for you to convert this code to SQL, and come back with any questions you might have, this is a Q&A remember not a contract work site.  

 

For guidance, case <> when <> else <> end, is equivant to if statements, and the last if statement is equivalent to a where clause. 

Frequent Contributor
Posts: 112

Re: proc sql help

RW9, Thanks for your response. I am not getting the same outputs. I am not using case when correctly I believe. could you please check it and let me know where I am messing it up.

 

proc sql;
create table one1 as
select a.*,b.*, case sum_1 when sum_1=. then sum_1=0 else sum_1 end as sum_1, (a.sum_1+b.sum_2) as difference
from one a, two b
where (a.variable1=b.variable1) and (a.variable2=b.variable2) and (calculated difference >5000) and (date eq today() or date2 eq today()) ; quit;

 

thanks in advance. 

Super User
Super User
Posts: 9,193

Re: proc sql help

 

 

It should look like:

case when sum_1=. then 0 else sum_1 end as sum_1,

(a.sum_1+b.sum_2) as difference

Super User
Posts: 12,994

Re: proc sql help

And Merge By is a form of Full Outer Join using the by variables in an ON clause.

 

However to exactly match merge behavior may require a lot of additional code to replicate the way merge results in the values for the non-explicitly named variables where the values for the second named dataset replace those of the first named when the by variables match.

Frequent Contributor
Posts: 112

Re: proc sql help

Is my case when statement good as for as my code is concerned? 

Frequent Contributor
Posts: 112

Re: proc sql help

Sorry, I didn't check RW9 response for case when statement. Let me try that. 

 

thanks,

 

Ask a Question
Discussion stats
  • 6 replies
  • 136 views
  • 1 like
  • 3 in conversation