topic Self Update a Column in SAS Programming

Self Update a Column

DavidPhillips2 — Thu, 02 Aug 2018 20:40:52 GMT

Is there an easy way to do something like:

proc sql;
	create table TEMP_PELL_ELIGIBLE as
	select distinct person_uid, degree_level
	from STU_GRAD_RETEN 
	where PELL_ELIGIBLE = 'Pell Eligible';
quit;

proc sort data= STU_GRAD_RETEN;
	by person_uid degree_level;
run;

proc sort data= TEMP_PELL_ELIGIBLE;
	by person_uid degree_level;
run;

data STU_GRAD_RETEN; merge STU_GRAD_RETEN (drop= PELL_ELIGIBLE) TEMP_PELL_ELIGIBLE;
	by person_uid degree_level;
	length PELL_ELIGIBLE $50.;
	if PELL_ELIGIBLE ne 'Pell Eligible' then 'Not Pell Eligible';
run;

Re: Self Update a Column

hashman — Thu, 02 Aug 2018 21:21:50 GMT

@DavidPhillips2:

I'm not sure how to specify ELSE with update but you can do it in two shots: (1) update the matching rows with "Pell Eligible" (just "E" in the sample below) and (2) update the remaining non-matching rows with "Not Pell Eligible" (just "N" below). E.g.:

data temp ;                                                        
  input uid ;                                                      
  cards ;                                                          
0                                                                  
2                                                                  
4                                                                  
5                                                                  
;                                                                  
run ;                                                              
                                                                   
data reten ;                                                       
  input uid elig $1. ;                                             
  cards ;                                                          
1 X                                                                
2 X                                                                
2 X                                                                
3 X                                                                
3 X                                                                
4 X                                                                
5 X                                                                
5 X                                                                
;                                                                  
run ;                                                              
                                                                   
proc sql ;                                                         
  update reten set elig = "E" where uid in (select uid from temp) ;
  update reten set elig = "N" where elig ne "E" ;                  
quit ;

Better SQL heads will surely tell you how to do it better. Alternatively, you can use a hash table to rewrite the master data set or create a new one and keep the original:

*data newreten ;
data reten ;                        
  if _n_ = 1 then do ;              
    dcl hash u (dataset:"temp") ;   
    u.definekey ("uid") ;           
    u.definedone () ;               
  end ;                             
  set reten ;                       
  if u.check() = 0 then elig = "E" ;
  else elig = "N" ;                 
run ;

Paul D.

Re: Self Update a Column

kiranv_ — Fri, 03 Aug 2018 01:08:42 GMT

you can do either by update statement or by doing a left join and creating a table


/* by update method*/
proc sql ;                                                         
  update reten a
  set elig =  case when a.uid in (select uid from temp) 
  then 'E' else 'N' end;                  
quit ; 

/* by creating new table*/

proc sql;
create table new as 
select a.uid, 
 case when a.uid = b.uid then 'E'
 else 'N'
 end as elig
from reten a
left join
temp b
on a.uid = b.uid;

Re: Self Update a Column

hashman — Fri, 03 Aug 2018 01:13:06 GMT

@kiranv_:

That's it! I knew some SQL head would straighten me out on conditional UPDATE. Thanks!

Paul D.

Re: Self Update a Column

kiranv_ — Fri, 03 Aug 2018 01:32:37 GMT

@hashman This is too kind of you. I just started hash learning/journey from this Monday after getting kindle version of your book. thank you.

Re: Self Update a Column

hashman — Fri, 03 Aug 2018 02:10:10 GMT

Ah, in this case the pleasure is all mine. And, I'm sure, Don's as well. Thanks for reading it: We definitely didn't write it just to let it gather dust on a bookshelf.

Best

Paul D.

Re: Self Update a Column

PGStats — Fri, 03 Aug 2018 04:43:39 GMT

If all you are doing is setting Not Pell Eligible when the value is not Pell Eligible then this update query should do it

proc sql;
update STU_GRAD_RETEN
set PELL_ELIGIBLE = "Not Pell Eligible"
where PELL_ELIGIBLE ne "Pell Eligible";
quit;

(untested)