Getting the names, types, formats, lengths, and labels of variables in a SAS data set
April 15, 2017 5 Comments
After reading my blog post on getting the variable names of a SAS data set, a reader named Robin asked how to get the formats as well. I asked SAS Technical Support for help, and a consultant named Jerry Leonard provided a beautiful solution using PROC SQL. Besides the names and formats of the variables, it also gives the types, lengths, and labels. Here is an example of how to do so with the CLASS data set in the built-in SASHELP library.
* add formats and labels to 3 of the variables in the CLASS data set; data class; set sashelp.class; format age 8. weight height 8.2 name $15.; label age = 'Age' weight = 'Weight' height = 'Height'; run; * extract the variable information using PROC SQL; proc sql noprint; create table class_info as select libname as library, memname as data_set, name as variable_name, type, length, format, label from dictionary.columns where libname = 'WORK' and memname = 'CLASS'; /* libname and memname values must be upper case */ quit; * print the resulting table; proc print data = class_info; run;
Here is the result of that PROC PRINT step in the Results Viewer. Notice that it also has the type, length, format, and label of each variable.
Obs | library | data_set | variable_name | type | length | format | label |
---|---|---|---|---|---|---|---|
1 | WORK | CLASS | Name | char | 8 | $15. | |
2 | WORK | CLASS | Sex | char | 1 | ||
3 | WORK | CLASS | Age | num | 8 | 8. | Age |
4 | WORK | CLASS | Height | num | 8 | 8.2 | Height |
5 | WORK | CLASS | Weight | num | 8 | 8.2 | Weight |
Thank you, Jerry, for sharing your tip!
looks great! But I’m getting a syntax error, where it underlines the “from” statement and says:
syntax error, expecting one of the following: a name, ;, (, ‘,’,’,ANSIMISS,AS,ORDER,OUTER,RIGHT,UNION,WHERE.
Any idea why?
Hi Emil,
I ran my SAS script exactly as it appears on my blog, and it ran perfectly with no syntax errors.
I don’t know what is causing your error, so I encourage to send your log to SAS Technical Support for help.
Please update us in this comment section if you get clarification about your error.
Thanks,
Eric
Pingback: Store multiple strings of text as a macro variable in SAS with PROC SQL and the INTO statement | A bunch of data
This has worked for me in the past, but today I’m getting the following error:
“ERROR: The following columns were not found in the contributing tables: format, label, length, name, type.”
Hi Emily – I’m not sure why you are getting this new error. I suggest you to replicate your error with a sample dataset and send the error message to SAS Technical Support. It usually responds within 24 hours, especially for something fairly obvious like this.
Good luck, and please share an update here if you do get an answer!