topic Re: proc sql help in SAS Programming

proc sql help

buddha_d — Wed, 15 Nov 2017 14:55:02 GMT

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.

Re: proc sql help

RW9 — Wed, 15 Nov 2017 15:20:13 GMT

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.

Re: proc sql help

buddha_d — Wed, 15 Nov 2017 15:50:45 GMT

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.

Re: proc sql help

ballardw — Wed, 15 Nov 2017 15:53:00 GMT

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.

Re: proc sql help

buddha_d — Wed, 15 Nov 2017 16:10:25 GMT

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

Re: proc sql help

RW9 — Wed, 15 Nov 2017 16:31:12 GMT

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

Re: proc sql help

buddha_d — Wed, 15 Nov 2017 16:39:22 GMT

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

thanks,