@RVO wrote:
Thanks for the replies; I don't understand however what you mean with custom formats to join? Could you maybe give a simple example?
Custom formats or informats can return values based on a single variable value. Consider that you have Site identification code that uniquely identifies a site (location/ person/object). You could have a format that returns specific items about that site based on the code value such as street address, city, state, postal code, manager name, business description, just about any piece of information.
Depending on need you can use the format directly in a procedure, such as proc print or create a new variable.
You should have the SASHELP.CLASS data set. This is small enough to demonstrate some use of formats instead of Join. First create the formats. Formats if stored in permanent libraries can be reused just like the formats SAS provides. They are relatively easy to make from data sets that have value pairs like a key, the variable you would join on in a query, and the value.
Proc format library=work;
value classhours
11,12 = '10AM - 2PM'
13,14,15 = '2PM - 4PM'
16 = '9AM - 3PM'
;
value $HistAssignment
'Alice','Jane','John' = 'Fall of Rome'
'Alfred','Henry','Janet','Philip' = 'Renaissance Painters'
other = 'Origins of Chocolate'
;
run;
To add variables with the values from the custom format:
data example;
set sashelp.class;
Classhours = put(age,classhours.);
HistoryAssignment = put(name,$histassignment.);
run;
This is similar to joining on one data set that has Age and classroom hours values and another dataset with name and the history assignment.
Another value of formats comes in analysis and reporting though. Quite often you join data and then count the results. That can be done directly in the SAS reporting procedures.
proc tabulate data=sashelp.class;
class age name;
format age classhours. name $histassignment.;
table age name,
n
;
run;
The reporting, analysis and most graphs can use the formatted values instead of actually adding a variable to the data set. This is a limitation though that only one format can be associated with a variable at the time of analysis or reporting.
Note that in the $Histassignment a special assignment rule 'other' was used. That means that any value not specifically listed, such as the seven names listed, gets a specific value assigned. In an SQL type query that would require adding a CASE statement on top for the "not specifically returned" by the Join values.
You can also use INVALUE statements to convert text to numbers with some more flexibility.
Consider a case where you have field staff that are supposed to enter an F or M for gender, or a . if not recorded. For some other purposes you need that to be a numeric value of 1, 2 or missing. But field staff being human some of them may enter upper case and others lower case letters.
An example of an informat to read such, provide some feed back about incorrect data entry, and assign expected values:
Proc format library=work;
invalue sexnum (upcase)
'F' = 1
'M' = 2
'.' = .
other = _error_
;
run;
data example;
input s :sexnum.;
datalines;
f
F
M
m
x
.
;
The option UPCASE on the Invalue says to upper case the text before comparing to the list of values. So that fixes case without a lot of work. Not much of a headache for single letters but some things this may save a lot of time.
Second the Other, which means any value other than the specified F, M or . is treated as invalid data and you get a diagnostic in the LOG showing the value of the line read with a ruler and the values of resulting variables.
I use this OTHER _error_ to read site location codes for a project where the staff will create new sites without telling the analyst. So when I encounter one of these I can contact the staff to get the metadata (that goes into other formats such as site name, location and such) OR correct the entry in the file. Update the invalue statement to add the new value to the acceptable list and rerun the program to validate.
An equivalent "query" approach would have to join on the value of the site (or sex code in the example) and then provide logic in the form of case or separate step to handle the exceptions.
Also, if you have multiple variables that have the same values then the same format can be used with multiple variables without a lot of work.
