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 @ewillisva
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 @ewillisva
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.