How to get formatted value of variable in its standard format without specifying it?

[ADoering]

In our work, we are using user defined formats extensively (short codes in the data, long labels in output). Drawback is that for programming/filtering, people still need to remember the raw value behind the formatted value shown otherwise. Of course they could filter for the formatted value by using the put function, only they would need to remember the format name, then, and always specify it.

Is there maybe a more efficient way I'm missing to apply the "standard format" according to the data set definition to the variable in programming without having to specify the format, or otherwise make the formatted value available?

Example:

/* some user defined format*/

proc format;

value companyfmt 1='Company A' 2='Company B' 3='Company C';

run;

/* a data set where one variable is formatted using that format */

data test;

input company variable $;

format company companyfmt.;

cards;

1 a

1 b

2 d

3 e

run;

/* to filter, this is what we currently do*/

proc print data=test(where=(company=1));

/* the alternative requires remembering the format name and a lot of typing */

proc print data=test(where=(put(company,companyfmt.)='Company A'));

/* ..and this does not work */

proc print data=test(where=(put(company)='Company A'));

Any ideas or best practice appreciated.

I'm on SAS 9.4M5.

Thanks and regards

Andy

[Shmuel]

Your main issue is "people still need to remember".

Are people programmers or users? 

Do people write their code or import code to adapt to their needs?

In last case - importing code to adapt - just add format documentation as remarks.

In more sophisticated tools, like SAS AF, you can use GUI tools to choose from a table.

[ADoering]

people are actuaries 🙂 which means they are both (end user programming), while obviously some are power users (frequent programming), others more occasionally programming (more using existing programs where I can use graphic prompts in SAS EG)

[Kurt_Bremser]

When you have people working together, you only need three things:

1. Documentation
2. Documentation
3. Documentation

and a culture that makes use of those.

[Ksharp]

Try function  vvalue() .

[Kurt_Bremser]

---

@Ksharp wrote:

Try function  vvalue() .

---

I thought about something similar, but these functions are only available in data steps, not in where conditions.

[Ksharp]

Ha. Kurt,

Create a view with vvalue() and proc print that view. Does it look good ?

/* some user defined format*/

proc format;
value companyfmt 1='Company A' 2='Company B' 3='Company C';
run;

 

/* a data set where one variable is formatted using that format */
data test;
input company variable $;
format company companyfmt.;
cards;
1 a
1 b
2 d
3 e
;

data x/view=x;
set test;
id=vvalue(company);
run;

/* the alternative requires remembering the format name and a lot of typing */
proc print data=x(where=(id='Company A'));run;

 

[andreas_lds]

Sometimes it is wise to apply formats in the reporting procedures only and not on datasets, but this doesn't avoid that users have to remember - in this case - the names of the formats. Depending on the size of the datasets and the number of formatted variables, maybe creating a view having both the formatted and the unformatted version of the variable is an option. By using a view the space on disc should not increase to heavily.

[FreelanceReinh]

If there are no more than, say, 200-300 pairs of variables and formats and they don't change frequently, the amount of typing and remembering could be reduced substantially by using keyboard macros (abbreviations) in the Enhanced Editor (or the equivalent SAS EG feature, if any). These could be distributed to the users in a .kmf file.

Example:

Menu Tools --> Add Abbreviation...

So, after typing "put(c" the editor would suggest the insertion shown above (confirm with Return key), where "Company A" could be the most frequently used company name.

This could be made even more convenient by enhancing the keyboard macro (created by adding this abbreviation) with a few cursor navigation and selection commands so that "Company A" would be highlighted automatically and hence could be overwritten immediately with a different company name:

Menu Tools --> Keyboard Macros --> Macros... --> Edit...

Finally, abbreviations for variable names starting with the same letter could be "grouped" together, e.g.:

put(c   --> put(company,companyfmt.)='Company A'

put(cc  --> put(country,countryfmt.)='United States'

put(ccc --> put(customer,customerfmt.)='Customer X'

Thus, the user can quickly step through the variable names (displayed in the yellow info text) in alphabetical, frequency or any other order just by repeating the first letter (and backwards with the Backspace key!).

[mkeintz]

Make a macro that inserts a format according to the variable name.  Then your users will have little to remember or type:

/* some user defined format*/ 
proc format; 
value companyfmt 1='Company A' 2='Company B' 3='Company C'; 
run; 

%macro fmt(vname);
  %if &vname=company %then put(company,companyfmt.) ; %else
  %if &vname=variable %then put(variable,$1.) ;
%mend;
 
/* a data set where one variable is formatted using that format */ 
data test; 
input company variable $; 
format company companyfmt.; 
put company=;
cards; 
1 a 
1 b 
2 d 
3 e 
run; 

proc print data=test(where=(%fmt(company)='Company A')); 
run; 

And this macro could be easily updated every time the dataset is revised (i.e. a new format is assigned).

Additional note:  You don't need to include every variable in the %fmt macro - just the ones where underlying value doesn't look like the formatted value.  Let's say you only have this problem with variable COMPANY.  All the other vars just use defaujlt formats, and therefore don't need a PUT function.  Then just set the %FMT macro to issue the var name, but no put function  (that's the "%ELSE &vname;" statement below:

/* some user defined format*/ 
proc format; 
value companyfmt 1='Company A' 2='Company B' 3='Company C'; 
run; 

%macro fmt(vname);
  %if &vname=company %then put(company,companyfmt.) ; %else
  &vname;
%mend;
 
/* a data set where one variable is formatted using that format */ 
data test; 
input company charvar $  numvar; 
format company companyfmt.; 
put (_all_) (=);
cards; 
1 a 101
1 b 102
2 d 103
3 e 104
run; 
proc print data=test(where=(%fmt(numvar)=103 or %fmt(charvar)='b')); 
run; 
proc print data=test(where=(%fmt(company)='Company A')); 
run; 

[unison]

You can probably adjust this to be more robust (right now, there are issues if multiple datasets have the same name but different formats).. But here's an idea!

/* some user defined format*/
proc format;
value companyfmt 1='Company A' 2='Company B' 3='Company C';
run;

/* a data set where one variable is formatted using that format */
data test;
input company variable $;
format company companyfmt.;
cards;
1 a
1 b
2 d
3 e
;
run;

*Stores result of var format into global macro var: _stdfmt;
%macro retreivefmt(var);

%global _stdfmt;
%local dsid;

%let dsid = %sysfunc(open(sashelp.vcolumn(where=(name="&var") obs=1)));
%if &dsid %then %do;
	%syscall set(dsid);
	%let rc=%sysfunc(fetchobs(&dsid,1));
	%let _stdfmt = &format;
 	%let dsid = %sysfunc(close(&dsid));
%end;

%put _stdfmt;
%mend;

%retreivefmt(company);
proc print data=test(where=(put(company,&_stdfmt.)='Company A'));

-unison

[ADoering]

Thanks everyone.

I take it there is no optimal solution (like vvalue() outside data step, or put() with a default), but I like the ideas of 

• having views with both formatted and raw values for our main tables
• creating a fmt% macro as presented by mkeintz, but combining with unison's suggestion to derive it automatically - since formats are unique to the variable name in that environment accross tables, this shouldn't be an issue - I'll see if I can even combine it with use of vvalue where applicable if not a variable in one of our standard tables. I'd post it when I get to do it - or accept it as a solution, when someone else does 🙂

---------------------------

EDIT

---------------------------

This would currently be my solution:

%Macro fmt(var);

%local dsid rc;
%let dsid = %sysfunc(open(sashelp.vcolumn(where=(upcase(name)=upcase("&var")) obs=1)));
%put &dsid;
%if &dsid %then %do;
%syscall set(dsid);
%let rc=%sysfunc(fetchobs(&dsid,1));
%let dsid = %sysfunc(close(&dsid));
%end;
%if not %symexist(format) %then %do;
&var
%end; %else %do;
put(&var, &format)
%end;

%mend;

/*Test&Examples*/
proc format;
value yesno 0='No' .=' ' other='Yes';
run;

data test;
format ayesno yesno.;
input ayesno;
b=%fmt(ayesno);
datalines;
1
0
1
run;

proc print data=test(where=(%fmt(ayesno)='No'));
run;

Thanks to mkeintz and in particular to unison for the technique to get the &format via fetchobs (at least that's where I think &format comes from, even though I could not spot anything in respect of return of variables as macro variables in the fetchobs-documentation).

[mkeintz]

---

@ADoering wrote:

Thanks everyone.

I take it there is no optimal solution (like vvalue() outside data step, or put() with a default), but I like the ideas of 

• having views with both formatted and raw values for our main tables
• creating a fmt% macro as presented by mkeintz, but combining with unison's suggestion to derive it automatically - since formats are unique to the variable name in that environment accross tables, this shouldn't be an issue - I'll see if I can even combine it with use of vvalue where applicable if not a variable in one of our standard tables. I'd post it when I get to do it - or accept it as a solution, when someone else does 🙂

---

I appreciate the idea of dynamically generating the format, using metadata in the data set header information (or maybe PROC SQL dictionary 

and I wonder whether a DOSUBL macro calling function would be the technique to use.  Unlike other uses of macros the it does not need to be resolved prior to execution of the call data step.  But I have never needed to find out more about DOSUBL.  If someone on this thread could prove or disprove my conjecture, I would be very interested.

[Tom]

So it looks like you are trying to solve the problem of how to filter the data when you know the formatted value but not the actual value.

One trick I have seen is to create a copy of your format library where all of your format labels (decoded values) have been converted to include the actual value also.  Then users know what value to use in the code.  When you are finished with coding/debugging just change the FMTSEARCH option to use the original format library and your reports will look nice again.

Something like this:

proc format cat=formats;
  value companyfmt 1='Company A' 2='Company B' 3='Company C';
run;
proc format cat=cformats;
  value companyfmt 1='1 Company A' 2='2 Company B' 3='3 Company C';
run;
* Use the code including formats ;
options fmtsearch=(cformats formats);
proc freq data=test; tables company; run;
* Switch back to using the normal formats ;
options fmtsearch=(formats);
proc freq data=test ;
 tables company ;
run;

It is not that hard to convert an existing format catalog by using PROC FORMAT to convert it to a dataset and then manipulate the dataset to change the labels and use PROC FORMAT to make the new catalog;