BookmarkSubscribeRSS Feed
buddha_d
Pyrite | Level 9

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.

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

buddha_d
Pyrite | Level 9

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

 

 

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

ballardw
Super User

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.

buddha_d
Pyrite | Level 9

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

buddha_d
Pyrite | Level 9

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

 

thanks,

 

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
  • 6 replies
  • 794 views
  • 1 like
  • 3 in conversation