Create User-Defined Formats and Apply Labels

[MB_Analyst]

Hi have an Excel file that outlines all my format labels/values, that looks like this:

start	fmtname	Label
1	health_fmt	Good
2	health_fmt	Bad
3	health_fmt	Missing
1	sex_fmt	Female
2	sex_fmt	Male

I would like to bring this Excel file in and create my own SAS format file, so I can keep applying the same labels to many datasets. In reality there are many categories of each fomat, so having it in one file would be helpful. I think I'm trying to replicate this (https://www.cdc.gov/tobacco/data_statistics/surveys/nats/pdfs/format-file-instructions.pdf), but I'm unsure how to create the .SAS7bcat and then apply the formats to my data sets. 

[Reeza]

https://documentation.sas.com/?docsetId=proc&docsetTarget=n1e19y6lrektafn1kj6nbvhus59w.htm&docsetVer...

Use CTNLIN on the PROC FORMAT statement to have SAS create the formats for you automatically. 

This will create the formats and you can add the code to your autoexec or save the catalog in a predefined location and modify your settings so that it searches that location first.

I don't recommend catalogs personally because they aren't transferable across systems so I use macro code to create the formats each time from a data set but that's a personal preference from someone who changes jobs often. 

---

@MB_Analyst wrote:

Hi have an Excel file that outlines all my format labels/values, that looks like this:

start	fmtname	Label
1	health_fmt	Good
2	health_fmt	Bad
3	health_fmt	Missing
1	sex_fmt	Female
2	sex_fmt	Male

I would like to bring this Excel file in and create my own SAS format file, so I can keep applying the same labels to many datasets. In reality there are many categories of each fomat, so having it in one file would be helpful. I think I'm trying to replicate this (https://www.cdc.gov/tobacco/data_statistics/surveys/nats/pdfs/format-file-instructions.pdf), but I'm unsure how to create the .SAS7bcat and then apply the formats to my data sets. 

---

[ballardw]

Can you create a data set from that Excel file at all?

Do you have any indication as to whether the START value is supposed to be numeric or character?

If the Start values are all numeric this should be easy as you would only need to add a Type variable with the value of "N".

If you have a mix of Character and Numeric values you would need a Type of 'C' for the character formats. Depending on your actual content that may or may not be easier to add in Excel.

And use proc format with the data set as a cntlin= option.

Proc format cntlin=yourcntlindataset;

run;

Makes the formats available in the current session.

If you place the formats in a permanent library, by specifying the Library= option in the proc format code, then updating your SAS system's FMTSEARCH path to look there means that you don't need to rerun the Proc Format code in each session. DO keep the data set to create the formats though.