topic Re: How do you manage many format value labels? in SAS Programming

How do you manage many format value labels?

fieldsa83 — Wed, 10 May 2017 20:45:08 GMT

Is there a good way to keep all of them organized?

We have about 100 variables, each with their own value formats. Right now they are all simply in 1 very large, unmanageable PROC FORMAT list.

Re: How do you manage many format value labels?

ballardw — Wed, 10 May 2017 21:05:06 GMT

1) Create a permanent library that every user can access where you want to store the formats

2) Reference that library on the Proc format statement (bonus: create a cntlout data set into the same library with the CNTLOUT option)

3) Set the format search path to include that library with an options statement or modify the settings for each user

Skeleton code:

libname MyFmt "path to library";

proc format library=MyFmt cntlout=MyFmt.Cntlout;

;

options fmtsearch=( work Sashelp MyFmt);

Once the format catalog is created in them MyFmt library you only need to run the Proc Format code when you add/change format definitions.

The Libname and Options could be in Autoexec.sas or other program run a the start of session or set in properties.

SAVE THE PROC FORMAT CODE. You may need to reexecute it when upgrading (or use the Cntlout data set to rebuild the format catalog)

Re: How do you manage many format value labels?

Astounding — Wed, 10 May 2017 21:04:04 GMT

Permanently save the formats. This means:

Understand the LIBRARY= option on PROC FORMAT, to permanently save formats. Look at how updating works, when you are changing a permanently saved format.
Understand the FMTSEARCH option to be able to access permanently saved formats.

Then make one person the gatekeeper. All enhancements to the formats must go through that person.

Re: How do you manage many format value labels?

Kurt_Bremser — Thu, 11 May 2017 07:33:16 GMT

The best option to preserve formats is the code you have, period. It is just text that can easily be saved, copied, versioned, and ported to other operating systems. (see Maxims 26 and 27)

Now, you might consider to do some things that make the code more readable. One of those might be to create data steps with datalines that create the cntlin datasets for proc format, so you have one data step per format.

Another often overlooked option is that your formats might actually be recreations of already existing domain tables in your production database. If that is the case, have the domains exported and write a program that converts these tables to cntlin datasets. If there is an option to drive your programs from data, use it (see Maxim 33).

Re: How do you manage many format value labels?

Peter_C — Thu, 11 May 2017 12:30:29 GMT

Kurt's proposal could be extended - in that production database have a view created which will deliver the cntlin data, only when required.

In support of @ballardw
To add a format library to the format search paths option, use something like
OPTION APPEND= FMTSEARCH mylib.mfmt_catalog ;
In this way we do not replace earlier modifications to the format search path.
(and see also OPTION INSERT= )

Re: How do you manage many format value labels?

fieldsa83 — Fri, 12 May 2017 02:53:21 GMT

I use proc tabulate with a lot of different variables.

Once I've loaded all the format value labels in to memory, is there an easy way to reference, or possibly automatically attach format value labels in proc tabulate without having to manually call them?

For example if i use sex and age in one table, and then industry and occupation in another, I don't want ot be manually writing out format sex sex. age age. ; and then redoing all that when my selected variables are changing frequently. Do you know what I mean?

Re: How do you manage many format value labels?

Tom — Fri, 12 May 2017 04:43:48 GMT

Add the FORMAT statement to the data step that creates the data.

data mylib.mydata ;
   infile 'mysourefile.csv' dsd firstobs=2 truncover ;
   length AGE 8 SEX $1 ;
   input age sex ;
   format age age. sex sex. ;
run;

Re: How do you manage many format value labels?

fieldsa83 — Fri, 12 May 2017 04:46:38 GMT

Thanks but is there a way to replace the FORMAT statement with something that automatically identifies all the variables that have been read in and apply the corresponding label? It seems tedious when I have 100 variables that are sometimes read in but sometimes not... I don't want to spend 10 minutes trying to figure out which formats need to be applied each time I want to create a table.

Re: How do you manage many format value labels?

Tom — Fri, 12 May 2017 05:04:00 GMT

Are you re-reading your text files over and over again? If so why?

First make SAS datasets from your data. It can be one or many datasets. It is this step that will define your AGE variable including the label and format that is attached to it.

Then when you want to make your presentation you can either just run from that table or build some temporary table by selecting values from from one or more dataset. In either case the formats will continue to be attached to the variables.

Re: How do you manage many format value labels?

fieldsa83 — Fri, 12 May 2017 05:21:20 GMT

The issue with that is the full dataset ends up being like 50gb (brings in hundreds of text files--each file representing 1 month of the series). So I find it easier just to read in the few text files for the months that I need with a handful of variables depending on the analysis.

Re: How do you manage many format value labels?

Tom — Fri, 12 May 2017 05:23:56 GMT

make a program or a view that reads the data and just pass in the list of files to read and/or variables to keep as parameters.

%let filelist=month1 month2 month3;
%let varlist=age sex ;

%include 'program that reads files';

Re: How do you manage many format value labels?

fieldsa83 — Fri, 12 May 2017 05:32:58 GMT

I have something like that already, but it's the value label formatting that is proving to be the most tedious part...

Edit: oh I see, you mean reference a central database. What I have now references the text files. Perhaps it would be advantageous to just have 1 single database but I fear it will be too large, and adding new data each month could add more difficulties.

Re: How do you manage many format value labels?

Tom — Fri, 12 May 2017 05:38:40 GMT

If you have 1 to 10,000 text files that all have the same structure then you only need to write the program that reads them once.

For example if your text files all live in the same folder your program could be as simple as something like this.

%let filelist='month1.txt' 'month2.txt';
%let keeplist=age sex ;

filename dir '/directory_name';

data want ;
  infile dir(&filelist) dsd truncover ;
  length id 8 age 8 sex $1 ;
  input id age sex ;
  label
   id='Unique id' 
   Age='Age at onset'
   Sex = 'Gender'
  ;
  format age age. sex sex. ;
  keep &keeplist;
run;

Re: How do you manage many format value labels?

fieldsa83 — Fri, 12 May 2017 05:51:22 GMT

Yes, for sure; that's basically what I have now.

The issue is that there are 100 variables, not just age and sex.

And so if I want to make a table that only uses like 20 of the 100 variables, I basically have them all in a list and comment out the ones I don't want to read in. However, as it is now, I'd have to do the same process (manually curating a list of variables and their corresponding value labels). It would be easier if I could just have something that reads in the data, then makes a list, then checks that list against a list of formats and then just does all that automatically.

So this reads in first quarter 2007 and 2017. But imagine the list of variables like 10x as long and so I just comment out most of them.

options minoperator mindelimiter=',';

%macro doloop;

data _null_;

/* Start year */ call symput ("startyear",'2007');

/* Start month */ call symput ("startmonth",'01');

/* End year */ call symput ("endyear",'2017');

/* End month */ call symput ("endmonth",'04');

/* Only specific months */ call symput /*("limitmonth",'');*/ ("limit",'and (&i in (2007,2017) and &month in(01,02,03,04))'); /*for all months in range leave everything between single quotes blank*/

stop;

run;

/* This section starts the do loop logic */

%do i=&startyear %to &endyear;

%do j=1 %to 12;

%if &j.=1 and &i.=&startyear %then %let j=&startmonth; /* on first loop, sets &startmonth (instead of defaulting to 1)*/

%let month=%sysfunc(putn(&j,z2.)); /* leading 0 for month if needed */

%let year=%sysfunc(substrn(&i.,3)); /* last 2 digits of year */

%if ( ((&endyear. ne &startyear) and (&i. ne &endyear)) OR (&i.=&endyear and &month. le &endmonth) ) /* continues if not end year OR if end year but less or equal to endmonth */

&limit /*continues if conditions from symput above are met*/

%then %do; /* continues... */

data TAB&month.&year.; /* start data step*/

%if &i>=2017 %then /* use pre-release folder if year >= 2017 */

%do;

infile "\\DIRECTORY1\20&year.\tab&month.&year..prn" lrecl=430 missover;

%end;

%else /* use main tabs folder if not >= 2017 */

%do;

infile "\\DIRECTORY2 \tab&month.&year..prn" lrecl=430 missover;

%end;

/*__ STEP 2: INPUT DESIRED VARIABLES _________________________________________________________________________________________________*/

input

@6 DWELID $18. /* Dwelling ID */

@29 SYEAR 4. /* Survey year - yyyy */

@29 SDATE 6. /* Survey year and month yyyymm */

@33 SMONTH 2. /* Survey month - mm */

@63 FINALWT 5. /* Final weight for respondent */

@119 AGE_TABS 3. /* Age of respondent as of end of

/* @125 EDUCLEV 1. /* Highest level of edu (break at 1990) */

...

; /*this semicolon needs to stay (ends the DATA step)*/

%include "\\DIRECTORY1\PROC_FORMAT2.sas"; /* brings in formats*/

run;

%end; /* end restriction for going past end month */

%end; %end; /* end main start/end month/year do loops */

%mend;

%doloop;

Re: How do you manage many format value labels?

Tom — Fri, 12 May 2017 05:51:35 GMT

There is no value in making the generation of the INPUT statement conditional if the goal is just which variables you want to KEEP.

Just make the KEEP statement variable.

Re: How do you manage many format value labels?

fieldsa83 — Fri, 12 May 2017 05:59:20 GMT

But the text file is flat format so I have to specify their position; they don't exist until that's done.

Edit: plus I like seeing all the available variables and their input position listed, otherwise I forget about them and have to search in all the documentation to find their details.

Anyway, thanks for the help. I will continue to think about these options...

I feel like I could do the formats by making a list of all vars, and then havng it do a sort of look up on a list of formats and then apply them if there's a match.

Re: How do you manage many format value labels?

Tom — Fri, 12 May 2017 06:04:02 GMT

Are you saying that the format of the file is DIFFERENT every month? That AGE appears in some location that depends on whether you are reading the data for January instead of March?

Look at the example program I posted before and pretend that the input statement defines and reads 10,000 variables instead of three. If the KEEP statement still only lists two of them then the generated DATASET will only have those two variables. Converting the text strings into variables is trivial in terms of execution time. It is the time it takes to read the text file from the disk that takes time. It doesn't save any time to read just two variables intead of a hundred.

Re: How do you manage many format value labels?

Tom — Fri, 12 May 2017 06:06:45 GMT

proc contents data=have noprint out=contents; run;
proc format noprint lib=mylib cntlout=formats; run;
proc sql noprint;
 create table list as
 select distinct a.name,b.fmtname
  from contents a , formats b
  where upcase(a.name) = upcase(b.fmtname)
  order by 1
;
quit;

Re: How do you manage many format value labels?

Kurt_Bremser — Fri, 12 May 2017 06:06:54 GMT

@fieldsa83 wrote:

I have something like that already, but it's the value label formatting that is proving to be the most tedious part...

Edit: oh I see, you mean reference a central database. What I have now references the text files. Perhaps it would be advantageous to just have 1 single database but I fear it will be too large, and adding new data each month could add more difficulties.

Then create a dataset for each month, with the month being part of the dataset name. When doing analysis over a series of months, create a simple datastep view (with keep list) over those months and run your analysis from that. The assignment of formats and labels is done once per month when the initial import is done.

Re: How do you manage many format value labels?

fieldsa83 — Fri, 12 May 2017 06:07:27 GMT

Oh I see, yes I was thinking reading the full text file would be long and the keep would not be as efficient as only reading in specific parts. This is not the case?

And so does the keep apply to the formats. Would I just list all the formats and the keep would just apply to the ones rom the same &keeplist?