Adding Data Labels via Reference Table

[ewillisva]

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?  

[ed_sas_member · Accepted solution]

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;

[ewillisva]

That worked. Hooray for simple solutions! Thanks

[ed_sas_member]

Thank you @ewillisva !

[ballardw]

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.