I have a transposed data set with 51 variables, and need help adding labels to 49 of them.
Before the transpose, I had a table such as this :
Property ID | Inspection | Defect Name | Defect Code | Count |
AB123000 | 983750 | Clogged Drain - Bathroom | 37593 | 2 |
AB123000 | 983750 | Damaged Wall | 482095 | 3 |
AB123000 | 983750 | Mold/Mildew | 439457 | 1 |
...
Here is the transposed table (as example):
Inspection | cnt | 37593 | 482095 | 439457 |
983750 | obs_cnt | 2 | 3 | 1 |
...
These are only arbitrary examples, the actual transposed data set has 51 variables.
Due to the length of the defect name field, I couldn't get the transpose procedure to work on it. It has 49 possible values, with round brackets and hyphens in many cases. Some of these are quite long as well. Therefore, I had to use the defect code which made it far simpler. However, the code field does not tell me much without a label. How can I use a reference table containing the defect code and the corresponding name to add labels to the 49 variables on the transposed table?
Hi @RandoDando
What that code answer your question?
The IDLABEL statement creates labels for the transposed variables.
data have;
infile datalines dlm=",";
input Property_ID $ Inspection Defect_Name:$50. Defect_Code Count;
datalines;
AB123000,983750,Clogged Drain - Bathroom,37593,2
AB123000,983750,Damaged Wall,482095,3
AB123000,983750,Mold/Mildew,439457,1
;
run;
proc transpose data=have out=want (drop=_:);
var count;
id Defect_Code;
idlabel Defect_Name;
by inspection;
run;
proc print data=want;
title "Without labels";
run;
proc print data=want label;
title "With labels";
run;
Hi @RandoDando
What that code answer your question?
The IDLABEL statement creates labels for the transposed variables.
data have;
infile datalines dlm=",";
input Property_ID $ Inspection Defect_Name:$50. Defect_Code Count;
datalines;
AB123000,983750,Clogged Drain - Bathroom,37593,2
AB123000,983750,Damaged Wall,482095,3
AB123000,983750,Mold/Mildew,439457,1
;
run;
proc transpose data=have out=want (drop=_:);
var count;
id Defect_Code;
idlabel Defect_Name;
by inspection;
run;
proc print data=want;
title "Without labels";
run;
proc print data=want label;
title "With labels";
run;
If you want a report that people read then it may be even easier:
proc tabulate data=have; class Inspection Defect_Name/missing; freq Count; table inspection, defect_name=''*n='' /misstext=' ' ; run;
data in the wide format is often harder to work with and, as shown above, reports can often do the "make it wide" as needed.
Proc Report with the defect_name as an across variable would also work.
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.