PROC SQL SYNTAX ?
in [SAS]
|
From: Tom Abernathy on 7 Jul 2010 22:47 That comes from not testing the code before posting.You could add a STOP at the end to prevent SAS from running around the loop again. The other way I normally do this is to avoid DO loop and just code statements as below. The order of the IF and SET statement is important to handle the case when your dataset has 0 obs. If you do not put the IF EOF line BEFORE the SET statement the ending stuff will not get written. data _null_; file 'output'; if _n_=1 then put 'Beginning stuff'; if eof then put 'Ending stuff'; set IN end=eof; put 'Stuff for each observation'; run; On Jul 7, 9:06 pm, theorbo <theo...(a)gmail.com> wrote: > Muriel, thanks for the macro - always handy to have more. > > Tom, one question regarding your solution ... > > In addition to my list of variable name changes in my created file, I > also ended up with an extra "RENAME" at the end ... does that have > something to do with the loop? I played around a bit but when I > couldn't figure it out I changed the code to only put "RENAME" when > _N_ = 1 but it was still a curious thing. Any ideas? > > On Jul 7, 11:17 am, muriel <murielf...(a)gmail.com> wrote: > > > > > You can try my macro to change variables prefix, see below: > > > /* macro alter prefix on selected variables, the parameters are: > > indsn is the original dataset containing variables to be renamed ; > > outdsn is name of the new dataset to deposit the renamed variables; > > keep_list is a list of variables that you don't want to change, for > > example, ID variables, please separate them by space; > > rename_list is a list of variables to be renamed, please separate > > them by space; > > oldprefix is the old prefix for the variables to be renamed; > > newprefix is new prefix that you'd rename the variables with */ > > > %macro alterprefix(indsn=, outdsn= , keep_list= , rename_list=, > > oldprefix= , newprefix= ); > > %let _i=1; > > %let ds=%sysfunc(open(&indsn.,i)); > > > data &outdsn.; > > set &indsn.( > > rename=( > > %if &ds. %then %do; > > %let old_token=%scan(&rename_list,&_i); > > %do %while ( &old_token.. ne %str() ); > > %if ( %substr(&old_token. , 1 , > > %length(&oldprefix.))=&oldprefix. ) %then %do; > > %let new_token=&newprefix.%substr(&old_token. , > > %eval(%length(&oldprefix.)+1)); > > &old_token. = &new_token. > > %let _i=%eval(&_i. +1); > > %let old_token=%scan(&rename_list,&_i); > > %end; > > %end; > > %end; > > ) > > keep = &keep_list. > > ); > > run; > > %let rc=%sysfunc(close(&ds)); > > %mend alterprefix; > > > Once include the macro, you can call the macro in program like this, > > > %macro alterprefix(indsn= old_dataset , outdsn= new_dataset, > > keep_list= ID year, rename_list= FP_Ab FP_Ac FP_Ad, oldprefix= FP_A, > > newprefix= PT_); > > > Regards, > > Muriel > > > On Jul 6, 6:00 pm, theorbo <theo...(a)gmail.com> wrote: > > > > I have been using code of the format I've copied below to rename a lot > > > of variables from the form, FP_* to PT_*. The problem is that the > > > number of variables, coupled with the length of the variable names is > > > now going to potentially max out the macro variable length > > > restriction. > > > > So, how can I change the last line to only grab a certain set of > > > variable names, say, the first half into the first macro variable. > > > Ideally I would have some kind of list ... LIKE 'FP_A: -- FP_L:' . > > > Any ideas? > > > > PROC SQL > > > NOPRINT; > > > select trim(name)||'=PT_'||substr(name, > > > 3) > > > into :varlist_A1 separated by ' > > > ' > > > from > > > DICTIONARY.COLUMNS > > > WHERE LIBNAME EQ "WORK" and MEMNAME EQ "SAMPLE_DATASET" > > > and upcase(name) like 'FP_%';- Hide quoted text - > > > - Show quoted text -
From: theorbo on 8 Jul 2010 08:18 Understood - thanks for explaining. On Jul 7, 10:47 pm, Tom Abernathy <tom.aberna...(a)gmail.com> wrote: > That comes from not testing the code before posting.You could add a > STOP at the end to prevent SAS from running around the loop again. > > The other way I normally do this is to avoid DO loop and just code > statements as below. > The order of the IF and SET statement is important to handle the case > when your dataset has 0 obs. If you do not put the IF EOF line BEFORE > the SET statement the ending stuff will not get written. > > data _null_; > file 'output'; > if _n_=1 then put 'Beginning stuff'; > if eof then put 'Ending stuff'; > set IN end=eof; > put 'Stuff for each observation'; > run; > > On Jul 7, 9:06 pm, theorbo <theo...(a)gmail.com> wrote: > > > > > Muriel, thanks for the macro - always handy to have more. > > > Tom, one question regarding your solution ... > > > In addition to my list of variable name changes in my created file, I > > also ended up with an extra "RENAME" at the end ... does that have > > something to do with the loop? I played around a bit but when I > > couldn't figure it out I changed the code to only put "RENAME" when > > _N_ = 1 but it was still a curious thing. Any ideas? > > > On Jul 7, 11:17 am, muriel <murielf...(a)gmail.com> wrote: > > > > You can try my macro to change variables prefix, see below: > > > > /* macro alter prefix on selected variables, the parameters are: > > > indsn is the original dataset containing variables to be renamed ; > > > outdsn is name of the new dataset to deposit the renamed variables; > > > keep_list is a list of variables that you don't want to change, for > > > example, ID variables, please separate them by space; > > > rename_list is a list of variables to be renamed, please separate > > > them by space; > > > oldprefix is the old prefix for the variables to be renamed; > > > newprefix is new prefix that you'd rename the variables with */ > > > > %macro alterprefix(indsn=, outdsn= , keep_list= , rename_list=, > > > oldprefix= , newprefix= ); > > > %let _i=1; > > > %let ds=%sysfunc(open(&indsn.,i)); > > > > data &outdsn.; > > > set &indsn.( > > > rename=( > > > %if &ds. %then %do; > > > %let old_token=%scan(&rename_list,&_i); > > > %do %while ( &old_token. ne %str() ); > > > %if ( %substr(&old_token. , 1 , > > > %length(&oldprefix.))=&oldprefix. ) %then %do; > > > %let new_token=&newprefix.%substr(&old_token. , > > > %eval(%length(&oldprefix.)+1)); > > > &old_token. = &new_token. > > > %let _i=%eval(&_i. +1); > > > %let old_token=%scan(&rename_list,&_i); > > > %end; > > > %end; > > > %end; > > > ) > > > keep = &keep_list. > > > ); > > > run; > > > %let rc=%sysfunc(close(&ds)); > > > %mend alterprefix; > > > > Once include the macro, you can call the macro in program like this, > > > > %macro alterprefix(indsn= old_dataset , outdsn= new_dataset, > > > keep_list= ID year, rename_list= FP_Ab FP_Ac FP_Ad, oldprefix= FP_A, > > > newprefix= PT_); > > > > Regards, > > > Muriel > > > > On Jul 6, 6:00 pm, theorbo <theo...(a)gmail.com> wrote: > > > > > I have been using code of the format I've copied below to rename a lot > > > > of variables from the form, FP_* to PT_*. The problem is that the > > > > number of variables, coupled with the length of the variable names is > > > > now going to potentially max out the macro variable length > > > > restriction. > > > > > So, how can I change the last line to only grab a certain set of > > > > variable names, say, the first half into the first macro variable. > > > > Ideally I would have some kind of list ... LIKE 'FP_A: -- FP_L:' . > > > > Any ideas? > > > > > PROC SQL > > > > NOPRINT; > > > > select trim(name)||'=PT_'||substr(name, > > > > 3) > > > > into :varlist_A1 separated by ' > > > > ' > > > > from > > > > DICTIONARY.COLUMNS > > > > WHERE LIBNAME EQ "WORK" and MEMNAME EQ "SAMPLE_DATASET" > > > > and upcase(name) like 'FP_%';- Hide quoted text - > > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
From: jake on 8 Jul 2010 10:10
Tom Abernathy wrote: > Why not just write the statements out to a file instead? > > filename rename temp; > data _null_; > file rename; > put 'RENAME '; > do until (eof); > set sashelp.vcolumn end=eof;; > where name=:'FP_' and memname='SAMPLE_DATASET' and libname='WORK'; > newname='FP_L'||substr(name,4); > put name '=' newname; > end; > put ';' ; > run; why does the below code create a "monster" temp file output ? without the x=1 it also spits out an extra rename and orphans it without completion the intended output was to rename only AGE and HEIGHT data _null_;x+1; file rename; if x=1 then put 'RENAME '; do until (eof); set sashelp.vcolumn end=eof; where name=:'A' or name=:'H' and memname='CLASS' and libname='SASHELP'; newname='_'||substr(name,1,4); put name '=' newname; end; put ';' ; run; RENAME AIR =_AIR AMOUNT =_AMOU Age =_Age Height =_Heig ATTRTYPE =_ATTR ATTRCLAS =_ATTR AUTOMETH =_AUTO ATTRTYPE =_ATTR ASCMGRID =_ASCM AMOUNT =_AMOU AMOUNT =_AMOU AMOUNT =_AMOU AMOUNT =_AMOU AMOUNT =_AMOU ACTUAL =_ACTU ACTUAL =_ACTU ACTUAL =_ACTU AMOUNT =_AMOU AMOUNT =_AMOU ATTRIBUT =_ATTR AIRRPMD =_AIRR AIRRPMT =_AIRR AREACODE =_AREA AR_AOK =_AR_A AR_AOK =_AR_A AR_AOK =_AR_A ALT =_ALT ATTRIBUT =_ATTR Age =_Age ; |