Date: Tue, 9 Feb 2010 14:26:03 -0600
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: Saving values in a format in text form
In-Reply-To: <54E01EE17F635448A5CE0C2253E23FF90195C543@CONSTANCE.DOH.OK>
Content-Type: text/plain; charset=ISO-8859-1
Look at the options CNTLOUT and CNTLIN on PROC FORMAT - they deal with how
you get the formats in a dataset and then back into the format catalog from
a dataset. That's probably the easiest way to deal with your root question.
-Joe
On Tue, Feb 9, 2010 at 2:20 PM, White, Svend A. <SvendW@health.ok.gov>wrote:
> I need to tweak some formats that are currently in use and as part of
> the process am extracting all the existing formats and creating a new
> setup script (which hopefully won't get lost this time).
>
> Here's the process I'm using.
> 1. Execute a bunch of PROC FORMAT statements like the above, one for
> each existing format.
> 2. Copy & paste all of the output into a text editor.
> 3. Use regular expressions to transform all that into a series of VALUE
> sets that can be plugged into a PROC FORMAT step to recreate the format
> collection.
>
> This inelegant process is working fine except for the truncation of
> longer values in the output window, which necessitates manual editing.
>
> Hence my three questions:
>
> 1) Is there a smarter way to do this? I know that in an RDBMS one
> usually employ dynamic SQL, for example, to generate data design layer
> SQL for populating a table. It's tedious, but sometimes it can save you
> a huge amount of work. Is there an analogous option here for creating a
> setup script out of an existing format collection?
>
> 2) Am I right in thinking that when one adds a new value set to a format
> collection one must re-create them all? My impression is that one cannot
> just add a single value set (e.g., adding county to a format collection
> already containing, city and state).
>
> 3) Assuming that this is how I have to do things, can one modify the
> output of this code so none of the values are truncated to make them fit
> on the screen (perhaps by outputting them to a text file)?
>
> proc format
> library = DEVDATA7.formats fmtlib;
> select mdc;
> run;
>
>
> Thanks.
>
>
>
> > -----Original Message-----
> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> > Mike Zdeb
> > Sent: Tuesday, February 09, 2010 1:09 PM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Re: How to attach a numeric index to column names for use in
> a
> > loop?
> >
> > hi ... OK, you win !!!
> >
> >
> > I tried the loop versus your code with a large data set
> > (well large length-wise ... 20 million obs, if not width-wise ... 10
> > variables)
> >
> > loop ... multiple PROC FREQs on 10 single variables (about 5 secs cpu
> > each)
> > ods output ... versus one PROC FREQ on all 10 variables (about 20 secs
> > cpu)
> >
> > the elapsed time difference was even more in favor of the "one time
> > PROC FREQ"
> >
> > the post-processing code is neat (coalescec to find the data ... nice)
> >
> >
> > so, in the words of Emily Litella ... "Never mind."
> >
> >
> > --
> > Mike Zdeb
> > U@Albany School of Public Health
> > One University Place
> > Rensselaer, New York 12144-3456
> > P/518-402-6479 F/630-604-1475
> >
> > > I'm not convinced that creating a data set for each variable is ever
> > > going to be an advantage when the desired result is a single
> > "stacked"
> > > data set. Depending on what the desired result finally needs to be
> > > there are a easy routes to the variable values. Here is one using
> > the
> > > F_ variables.
> > >
> > > ods listing close;
> > > proc freq data=sashelp.class;
> > > ods output onewayfreqs=onewayfreqs;
> > > run;
> > > Ods listing;
> > > data results(keep=variable label value Frequency Percent Cum:);
> > > length variable $32 label $256 value $64;
> > > set oneWayFreqs;
> > > variable = scan(table,-1);
> > > label = vlabelX(variable);
> > > value = coalesceC(of F_:);
> > > run;
> > > proc print;
> > > run;
> > >
> > > On 2/9/10, Mike Zdeb <msz03@albany.edu> wrote:
> > >> hi ... if you want to go this route, here's another idea
> > >> with just the OUT= optiion in PROC FREQ
> > >> (I think that you end up with the same data set)
> > >>
> > >> and if you weren't skipping variables, put the SQL part inside the
> > macro and add
> > >> arguments to the macro call ... libname and data set name
> > >>
> > >>
> > >>
> > >> proc sql noprint;
> > >> select name, count(*)
> > >> into :varlist separated by '/', :nvars
> > >> from dictionary.columns
> > >> where libname eq 'SASHELP' and memname eq 'CLASS' and upcase(name)
> > ne 'NAME';
> > >> quit;
> > >>
> > >> %macro get_freqs;
> > >> proc datasets lib=work nolist;
> > >> delete results_all;
> > >> quit;
> > >>
> > >> %do j=1 %to &nvars;
> > >> %let v=%scan(&varlist,&j,'/');
> > >> proc freq data=sashelp.class;
> > >> table &v / noprint out=results;
> > >> run;
> > >>
> > >> data results;
> > >> length variable $32 value $15;
> > >> retain variable "&v" ;
> > >> set results;
> > >> value = cat(&v);
> > >> if vtype(&v) eq 'N' then value=right(value);
> > >> drop &v;
> > >> run;
> > >>
> > >> proc append base=results_all data=results;
> > >> run;
> > >> %end;
> > >>
> > >> proc datasets lib=work nolist;
> > >> delete results;
> > >> quit;
> > >> %mend;
> > >>
> > >> %get_freqs;
> > >>
> > >> --
> > >> Mike Zdeb
> > >> U@Albany School of Public Health
> > >> One University Place
> > >> Rensselaer, New York 12144-3456
> > >> P/518-402-6479 F/630-604-1475
> > >>
> > >> > And here is how to to multiple procedures with variables; this
> > demonstrates getting a frequency on every variable i the set; but
> other
> > >> procedures
> > >> > could be placed inside the loop as well. Note the %do loop has
> to
> > be inside a macro wrapper for this to work.
> > >> >
> > >> > ods output variables=variables;
> > >> > proc contents data=setname;
> > >> > run;
> > >> >
> > >> > proc sql noprint;
> > >> > create table variables_to_process as
> > >> > select variables.variable
> > >> > from variables
> > >>
> > >> > where variable not in ('ID')
> > >> > order by num;
> > >> > quit;
> > >> >
> > >> > data variables_to_process;
> > >> > set variables_to_process;
> > >> > runnum + 1;
> > >> > run;
> > >> >
> > >> > %macro get_freqs;
> > >> >
> > >> > data results_all;
> > >> > informat variable $50. value $50. frequency best32. percent
> > best32.
> > >> > cumfrequency best32. cumpercent best32.;
> > >> > stop;
> > >> > run;
> > >> >
> > >> > proc sql noprint;
> > >> > select max(runnum) into :max from variables_to_process;
> > >> > quit;
> > >> >
> > >> > %do i=1 %to &max;
> > >> > proc sql noprint;
> > >> > select variable into :variable
> > >> > from variables_to_process
> > >> > where runnum=&i;
> > >> > quit;
> > >> > %put processing variable: &variable;
> > >> >
> > >> > ods output onewayfreqs=onewayfreqs;
> > >> > proc freq data=setname;
> > >> > tables &variable;
> > >> > run;
> > >> >
> > >> > data results;
> > >> > informat variable $50. value $50. frequency best32. percent
> > best32.
> > >> > cumfrequency best32. cumpercent best32.;
> > >> > set onewayfreqs;
> > >> > variable="&variable";
> > >> > value=&variable;
> > >> > keep variable value frequency percent cumfrequency cumpercent;
> > >> > run;
> > >> >
> > >> > proc append base=results_all data=results;
> > >> > run;
> > >> > %end;
> > >> >
> > >> > %mend get_freqs;
> > >> >
> > >> > %get_freqs;
> > >> >
> > >> >
> > >> >
> > >> > --- mlhoward@avalon.net wrote:
> > >> >
> > >> > From: Mary <mlhoward@avalon.net>
> > >> > To: Church <H.J.Plat@UVA.NL>
> > >> > Cc: <SAS-L@LISTSERV.UGA.EDU>
> > >> > Subject: Re: How to attach a numeric index to column names for
> use
> > in a loop?
> > >> > Date: Tue, 9 Feb 2010 08:33:56 -0800
> > >> >
> > >> > You can save the variable names to a data set; this is one way:
> > >> >
> > >> > ods output variables=variables;
> > >> > proc contents data=setname;
> > >> > run;
> > >> >
> > >> > Then you can select the variables needed into a macro variable:
> > >> >
> > >> > proc sql noprint;
> > >> > select variable into :variable_list
> > >> > from variables
> > >> > separated by ' '
> > >> > where variable not in ('id');
> > >> > quit;
> > >> > %put &variable_list;
> > >> >
> > >> > data setname2;
> > >> > set setname;
> > >> > array vararray{*} &variable_list;
> > >> > do i=1 to dim(vararray);
> > >> > if vararray[i]= .... then vararray[i]=....;
> > >> > end;
> > >> > run;
> > >> >
> > >> >
> > >> > If you need to do something outside of a data step- then write
> > back- I usually do this in a macro and *pop* the variable list off to
> > process
> > >> each
> > >> > variable.
> > >> >
> > >> >
> > >> > -Mary
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > --- H.J.Plat@UVA.NL wrote:
> > >> >
> > >> > From: Church <H.J.Plat@UVA.NL>
> > >> > To: SAS-L@LISTSERV.UGA.EDU
> > >> > Subject: Re: How to attach a numeric index to column names
> > for use in a loop?
> > >> > Date: Tue, 9 Feb 2010 03:18:21 -0800
> > >> >
> > >> > On 9 feb, 12:03, Chris Jones <chris...@gmail.com> wrote:
> > >> >> On 9 Feb, 07:52, Church <H.J.P...@uva.nl> wrote:
> > >> >>
> > >> >> > Hi,
> > >> >>
> > >> >> > I have a dataset of about 100 columns.
> > >> >> > I use a loop where each time I need a different column. I
> don't
> > want
> > >> >> > to write the 100 column names in the code, but rather do it in
> > some
> > >> >> > more standardized way, for example by attaching an index
> number
> > to
> > >> >> > each column and then referring to this column using an index
> > variable
> > >> >> > (so for example do i=1 to n; and than selecting column i each
> > time).
> > >> >> > Does anyone know how to do this?
> > >> >>
> > >> >> > Thanks.
> > >> >>
> > >> >> Use arrays.
> > >> >>
> > >> >> In your initial data, create the columns with numerical
> suffixes,
> > e.g.
> > >> >>
> > >> >> data mydata ;
> > >> >> array n{*} d1-d100 ; /* create variables d1 thru to d100 */
> > >> >> /* further processing */
> > >> >> run ;
> > >> >>
> > >> >> data myprocess ;
> > >> >> set mydata ;
> > >> >> array n{*} d1-d100 ;
> > >> >>
> > >> >> do i = 1 to dim(n) ;
> > >> >> /* to reference d1 use n{1}, d2 > n{2}, etc. */
> > >> >> /* do something with each element */
> > >> >> /* e.g. */
> > >> >> n{i} = n{i} * 2 ;
> > >> >> end ;
> > >> >> run ;
> > >> >
> > >> > Thanks. That sounds good, but I have original variables with
> > original
> > >> > variable names that I first have to assign to the array elements
> > in
> > >> > this case. So then I still need to write the column name, don't
> I?
> > >> >
> > >>
> > >
>
| 
