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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.