PROC SQL SYNTAX ?
in [SAS]
|
From: theorbo on 6 Jul 2010 18:00 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_%';
From: Tom Abernathy on 6 Jul 2010 19:26 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; data new; set sample_dataset ; %inc rename; run; 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_%';
From: theorbo on 7 Jul 2010 00:43 Thanks, Tom. I'll try that method. On Jul 6, 7:26 pm, Tom Abernathy <tom.aberna...(a)gmail.com> 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; > > data new; set sample_dataset ; > %inc rename; > run; > > 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: muriel on 7 Jul 2010 11:17 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_%';
From: theorbo on 7 Jul 2010 21:06
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 - |