proc sql update process

Reply
Contributor
Posts: 26

proc sql update process

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

 

proc sql;

update table

set

field3=field2-field1,

field2=0;

quit;

 

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

 

data weight;
    input IDNUMBER $ Week1 Week2;
    WeightLoss2=Week1-Week2;
    datalines;
2477 195 163
2431 220 198
2456 173 155
2412 135 116
;

proc print data=weight;
run;

proc sql;
alter table weight
    add wl3 decimal(5,2), notes char(255);
quit;

proc sql;
update weight
    set
    wl3=week1-week2,
    notes=catx(', ',wl3, week1, week2),
    week1=0;
quit;
    
proc print data=weight;
run;

proc sql;
drop table weight;
quit;

 

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

 

thanks

Contributor
Posts: 26

Re: proc sql update process

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

Super User
Super User
Posts: 7,727

Re: proc sql update process

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;
Contributor
Posts: 26

Re: proc sql update process

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. 

Contributor
Posts: 26

Re: proc sql update process

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. 

Ask a Question
Discussion stats
  • 4 replies
  • 92 views
  • 0 likes
  • 2 in conversation