How would I create a program to do a repetitive process?
in [SAS]
|
From: DH on 14 Feb 2007 14:35 Hello all, I'm trying to find an easier way to add a new field to multiple tables; greater than 500. Copy the some data to that new field. Replace to old data with new data. Has anyone ever done something like this? I'm envisioning a Windows script for this process so that it will work on many different tables with differing names. More along the lines of a batch file running SAS proc SQL from the command line. Any help would be greatly appreciated. system: Windows XP, SAS: SAS 9.1 Here is an example: proc sql; *drop OrigId in case it already exists; alter table Course.classes drop OrigId; *add OrigId char*9 to current table; alter table Course.classes add OrigIdNum char(9); *add old idnum to OrigId; update Course.classes set OrigId = idnum; quit; proc sql; update Course.course set IDNUM = (select case when fid.ssn is missing then "" else fid.NewID end as lidnum from final_id as fid where Course.OrigId = fid.ssn); quit; proc sql; select idnum, origId from Course.classes where idnum is missing; quit;
From: Paige Miller on 14 Feb 2007 14:57 On Feb 14, 2:35 pm, "DH" <sheehan...(a)yahoo.com> wrote: > Hello all, > > I'm trying to find an easier way to add a new field to multiple > tables; greater than 500. > Copy the some data to that new field. > Replace to old data with new data. > > Has anyone ever done something like this? > > I'm envisioning a Windows script for this process so that it will work > on many different tables with differing names. > More along the lines of a batch file running SAS proc SQL from the > command line. > > Any help would be greatly appreciated. > > system: Windows XP, > SAS: SAS 9.1 > > Here is an example: > proc sql; > *drop OrigId in case it already exists; > alter table Course.classes > drop OrigId; > *add OrigId char*9 to current table; > alter table Course.classes > add OrigIdNum char(9); > *add old idnum to OrigId; > update Course.classes > set OrigId = idnum; > quit; > proc sql; > update Course.course > set IDNUM = > (select > case when fid.ssn is missing then "" else fid.NewID end as lidnum > from final_id as fid > where Course.OrigId = fid.ssn); > quit; > > proc sql; > select idnum, origId from Course.classes > where idnum is missing; > quit; SAS Macros will do this for you. Somehow, you will have to inform SAS the name of the 500 tables you want to change. I'm guessing you will have SAS read the names of the tables from a text file, spreadsheet or database file into a SAS data set. From there, you would create 500 macro variables, and then have the macro do the looping. So let's suppose you have already read the names of the 500 or so tables into a SAS data set called table_names, and the actual name is in the SAS variable name. This example assumes there aren't exactly 500 tables, it computes the number of tables from the number of rows in data set table_names. Here we go... %macro do_alot; /* Begin macro */ /* Create macro variables */ data _null_; set table_names end=eof; /* Next line creates macro variables name1 through nameNNN where NNN is the number of rows in table_names, each containing the name of one of the tables */ call symput('name'||left(_n_),name); /* Next line creates a macro variable containing the total number of rows in the SAS data set table_names */ if eof then call symput('ntables',left(_n_)); run; /* Next, loop &ntables times */ %do ii=1 %to &ntables; proc sql; /* Do SQL on table &&name&ii (note the double-ampersand) */ create table something as select * from &&nameⅈ /* use whatever SQL suits your needs */ quit; %end; /* End of %do loop */ %mend; /* End of macro */ /* Call the macro */ %do_alot
From: DH on 15 Feb 2007 12:18 On Feb 14, 12:57 pm, "Paige Miller" <paige.mil...(a)kodak.com> wrote: > On Feb 14, 2:35 pm, "DH" <sheehan...(a)yahoo.com> wrote: > > > > > Hello all, > > > I'm trying to find an easier way to add a new field to multiple > > tables; greater than 500. > > Copy the some data to that new field. > > Replace to old data with new data. > > > Has anyone ever done something like this? > > > I'm envisioning a Windows script for this process so that it will work > > on many different tables with differing names. > > More along the lines of a batch file running SAS proc SQL from the > > command line. > > > Any help would be greatly appreciated. > > > system: Windows XP, > > SAS: SAS 9.1 > > > Here is an example: > > proc sql; > > *drop OrigId in case it already exists; > > alter table Course.classes > > drop OrigId; > > *add OrigId char*9 to current table; > > alter table Course.classes > > add OrigIdNum char(9); > > *add old idnum to OrigId; > > update Course.classes > > set OrigId = idnum; > > quit; > > proc sql; > > update Course.course > > set IDNUM = > > (select > > case when fid.ssn is missing then "" else fid.NewID end as lidnum > > from final_id as fid > > where Course.OrigId = fid.ssn); > > quit; > > > proc sql; > > select idnum, origId from Course.classes > > where idnum is missing; > > quit; > > SAS Macros will do this for you. Somehow, you will have to inform SAS > the name of the 500 tables you want to change. I'm guessing you will > have SAS read the names of the tables from a text file, spreadsheet or > database file into a SAS data set. From there, you would create 500 > macro variables, and then have the macro do the looping. > > So let's suppose you have already read the names of the 500 or so > tables into a SAS data set called table_names, and the actual name is > in the SAS variable name. This example assumes there aren't exactly > 500 tables, it computes the number of tables from the number of rows > in data set table_names. Here we go... > > %macro do_alot; /* Begin macro */ > /* Create macro variables */ > data _null_; > set table_names end=eof; > /* Next line creates macro variables name1 through nameNNN where > NNN is the number of rows in table_names, each containing the name of > one of the tables */ > call symput('name'||left(_n_),name); > /* Next line creates a macro variable containing the total number > of rows in the SAS data set table_names */ > if eof then call symput('ntables',left(_n_)); > run; > /* Next, loop &ntables times */ > %do ii=1 %to &ntables; > proc sql; > /* Do SQL on table &&name&ii (note the double-ampersand) */ > create table something as select * from &&nameⅈ > /* use whatever SQL suits your needs */ > quit; > %end; /* End of %do loop */ > %mend; /* End of macro */ > > /* Call the macro */ > %do_alot Thanks Paige. Unfortunately I don't have a list of all the tables yet. Is there a way to do that in SAS? Would I need to create Lib Refs for each of the folders containing those tables? The tables are spread across multiple folders as well. All of the SAS data directories are contained in one main directory. Thanks again, Richard
From: Paige Miller on 15 Feb 2007 13:02 On Feb 15, 12:18 pm, "DH" <sheehan...(a)yahoo.com> wrote: > On Feb 14, 12:57 pm, "Paige Miller" <paige.mil...(a)kodak.com> wrote: > > > > > On Feb 14, 2:35 pm, "DH" <sheehan...(a)yahoo.com> wrote: > > > > Hello all, > > > > I'm trying to find an easier way to add a new field to multiple > > > tables; greater than 500. > > > Copy the some data to that new field. > > > Replace to old data with new data. > > > > Has anyone ever done something like this? > > > > I'm envisioning a Windows script for this process so that it will work > > > on many different tables with differing names. > > > More along the lines of a batch file running SAS proc SQL from the > > > command line. > > > > Any help would be greatly appreciated. > > > > system: Windows XP, > > > SAS: SAS 9.1 > > > > Here is an example: > > > proc sql; > > > *drop OrigId in case it already exists; > > > alter table Course.classes > > > drop OrigId; > > > *add OrigId char*9 to current table; > > > alter table Course.classes > > > add OrigIdNum char(9); > > > *add old idnum to OrigId; > > > update Course.classes > > > set OrigId = idnum; > > > quit; > > > proc sql; > > > update Course.course > > > set IDNUM = > > > (select > > > case when fid.ssn is missing then "" else fid.NewID end as lidnum > > > from final_id as fid > > > where Course.OrigId = fid.ssn); > > > quit; > > > > proc sql; > > > select idnum, origId from Course.classes > > > where idnum is missing; > > > quit; > > > SAS Macros will do this for you. Somehow, you will have to inform SAS > > the name of the 500 tables you want to change. I'm guessing you will > > have SAS read the names of the tables from a text file, spreadsheet or > > database file into a SAS data set. From there, you would create 500 > > macro variables, and then have the macro do the looping. > > > So let's suppose you have already read the names of the 500 or so > > tables into a SAS data set called table_names, and the actual name is > > in the SAS variable name. This example assumes there aren't exactly > > 500 tables, it computes the number of tables from the number of rows > > in data set table_names. Here we go... > > > %macro do_alot; /* Begin macro */ > > /* Create macro variables */ > > data _null_; > > set table_names end=eof; > > /* Next line creates macro variables name1 through nameNNN where > > NNN is the number of rows in table_names, each containing the name of > > one of the tables */ > > call symput('name'||left(_n_),name); > > /* Next line creates a macro variable containing the total number > > of rows in the SAS data set table_names */ > > if eof then call symput('ntables',left(_n_)); > > run; > > /* Next, loop &ntables times */ > > %do ii=1 %to &ntables; > > proc sql; > > /* Do SQL on table &&name&ii (note the double-ampersand) */ > > create table something as select * from &&nameⅈ > > /* use whatever SQL suits your needs */ > > quit; > > %end; /* End of %do loop */ > > %mend; /* End of macro */ > > > /* Call the macro */ > > %do_alot > > Thanks Paige. > > Unfortunately I don't have a list of all the tables yet. Is there a > way to do that in SAS? > Would I need to create Lib Refs for each of the folders containing > those tables? The tables are spread across multiple folders as well. > All of the SAS data directories are contained in one main directory. > > Thanks again, > > Richard You have been very vague about where and what these tables are. Are they text files? Spreadsheets? Databases? SAS Data Sets? -- Paige Miller paige.miller(a)kodak.com
From: DH on 15 Feb 2007 13:19 On Feb 15, 11:02 am, "Paige Miller" <paige.mil...(a)kodak.com> wrote: > On Feb 15, 12:18 pm, "DH" <sheehan...(a)yahoo.com> wrote: > > > > > On Feb 14, 12:57 pm, "Paige Miller" <paige.mil...(a)kodak.com> wrote: > > > > On Feb 14, 2:35 pm, "DH" <sheehan...(a)yahoo.com> wrote: > > > > > Hello all, > > > > > I'm trying to find an easier way to add a new field to multiple > > > > tables; greater than 500. > > > > Copy the some data to that new field. > > > > Replace to old data with new data. > > > > > Has anyone ever done something like this? > > > > > I'm envisioning a Windows script for this process so that it will work > > > > on many different tables with differing names. > > > > More along the lines of a batch file running SAS proc SQL from the > > > > command line. > > > > > Any help would be greatly appreciated. > > > > > system: Windows XP, > > > > SAS: SAS 9.1 > > > > > Here is an example: > > > > proc sql; > > > > *drop OrigId in case it already exists; > > > > alter table Course.classes > > > > drop OrigId; > > > > *add OrigId char*9 to current table; > > > > alter table Course.classes > > > > add OrigIdNum char(9); > > > > *add old idnum to OrigId; > > > > update Course.classes > > > > set OrigId = idnum; > > > > quit; > > > > proc sql; > > > > update Course.course > > > > set IDNUM = > > > > (select > > > > case when fid.ssn is missing then "" else fid.NewID end as lidnum > > > > from final_id as fid > > > > where Course.OrigId = fid.ssn); > > > > quit; > > > > > proc sql; > > > > select idnum, origId from Course.classes > > > > where idnum is missing; > > > > quit; > > > > SAS Macros will do this for you. Somehow, you will have to inform SAS > > > the name of the 500 tables you want to change. I'm guessing you will > > > have SAS read the names of the tables from a text file, spreadsheet or > > > database file into a SAS data set. From there, you would create 500 > > > macro variables, and then have the macro do the looping. > > > > So let's suppose you have already read the names of the 500 or so > > > tables into a SAS data set called table_names, and the actual name is > > > in the SAS variable name. This example assumes there aren't exactly > > > 500 tables, it computes the number of tables from the number of rows > > > in data set table_names. Here we go... > > > > %macro do_alot; /* Begin macro */ > > > /* Create macro variables */ > > > data _null_; > > > set table_names end=eof; > > > /* Next line creates macro variables name1 through nameNNN where > > > NNN is the number of rows in table_names, each containing the name of > > > one of the tables */ > > > call symput('name'||left(_n_),name); > > > /* Next line creates a macro variable containing the total number > > > of rows in the SAS data set table_names */ > > > if eof then call symput('ntables',left(_n_)); > > > run; > > > /* Next, loop &ntables times */ > > > %do ii=1 %to &ntables; > > > proc sql; > > > /* Do SQL on table &&name&ii (note the double-ampersand) */ > > > create table something as select * from &&nameⅈ > > > /* use whatever SQL suits your needs */ > > > quit; > > > %end; /* End of %do loop */ > > > %mend; /* End of macro */ > > > > /* Call the macro */ > > > %do_alot > > > Thanks Paige. > > > Unfortunately I don't have a list of all the tables yet. Is there a > > way to do that in SAS? > > Would I need to create Lib Refs for each of the folders containing > > those tables? The tables are spread across multiple folders as well. > > All of the SAS data directories are contained in one main directory. > > > Thanks again, > > > Richard > > You have been very vague about where and what these tables are. Are > they text files? Spreadsheets? Databases? SAS Data Sets? > > -- > Paige Miller > paige.mil...(a)kodak.com Sorry, I very much a newbie with SAS. The tables are SAS Data Sets. The are located on a SAMBA mounted shared drive (F:\OIRRPTS\zSASdata\) While SAS 9.1 for windows is located on the hard drive (C:\Program Files\SAS) On the SAMBA shared drive, there are 40 some folders that contain grouped data. One of those folders is ENROLLED (F:\OIRRPTS\zSASdata\ENROLLED). Another folder could be COURSES(F:\OIRRPTS\zSASdata\COURSES). In each folder, there can be hundreds of SAS data sets (F:\OIRRPTS \zSASdata\COURSES\crsF1001.sas7bdat, .\crsF1007.sas7bdat, . \crsF1403.sas7bdat, etc. not necessarily sequential) I hope this better explains my circumstances. regards, Richard
|
Next
|
Last
Pages: 1 2 Prev: proc sql note and attributes. Next: Q: resolving Windows environment variables |