07-05-2017 10:44 AM
i know that there are weird things that happen sometimes in sql when you calculate a field from other fields and then have to update on of those fields to zero...for instance, .
so in this instance like in sql server it will update the 0 first and then the calculated field so i built an little code to test it and the oddest of all results came out. i obviously can split these into two queries but would rather do it in one and understand the process. so here is the code...
input IDNUMBER $ Week1 Week2;
2477 195 163
2431 220 198
2456 173 155
2412 135 116
proc print data=weight;
alter table weight
add wl3 decimal(5,2), notes char(255);
notes=catx(', ',wl3, week1, week2),
proc print data=weight;
drop table weight;
what it appears to do is update notes first, then wl3 and then week1. i cannot figure out why it would update notes first. is it because that is a concatenated field or the formula? does anyone understand how they do that because it really doesnt do it in order...
07-05-2017 10:47 AM
oh just to clarify, i need to use a field in a calculation and then set that field to zero after. like i said i know i can accmoplish that in two queries but know it is possible in some sqls to do it in one...
07-05-2017 11:05 AM
SQL is passed to the SQL parser which then breaks the SQL out into tasks, so yes its quite likely that the catx() is performed first and the assignements are done later, you can see the process by putting:
proc sql _tree;
Your mixing up SQL processing with SAS processing (which is the catx).
In your code. The question is, why use proc sql update for this task? Use Base SAS.
data weight; set weight; wl3=week1-week2; notes=catx(',',week1,week2); week1=0; run;
07-05-2017 11:40 AM
the reason i am using proc sql is because i am new to sas but extremely knowledgable about sql. plus, since i am doing a lot of steps that i could not find a way to do in a data step, i just started using proc sql for everything. however at times i am using data steps where i cannot use proc sql...
i know catx is sas base but it was the only thing that i could find to properly concantenate data in two columns the way i wanted. it appears that coelesce might work after doing research on this though. but to be honest, i really dont care as long as i have something working well and i understand explicity how it works.
the question i have on that data step though, does it explicitly process each of those in the order written or does it work in some other heirarchal order? that is the important thing, me understanding how it is going to process the updates.
07-05-2017 12:58 PM
also, i was testing the _tree to see if it noted what order it was processing the updates in and i could not tell if it was. so if someone knows, please let me know.