BookmarkSubscribeRSS Feed
me55
Quartz | Level 8

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

4 REPLIES 4
me55
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
me55
Quartz | Level 8

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. 

me55
Quartz | Level 8

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. 

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
  • 4 replies
  • 487 views
  • 0 likes
  • 2 in conversation