topic Adding Data Labels via Reference Table in SAS Procedures

Adding Data Labels via Reference Table

RandoDando — Mon, 04 May 2020 13:35:47 GMT

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?

Re: Adding Data Labels via Reference Table

ed_sas_member — Mon, 04 May 2020 13:43:03 GMT

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;

Re: Adding Data Labels via Reference Table

RandoDando — Mon, 04 May 2020 13:45:32 GMT

That worked. Hooray for simple solutions! Thanks

Re: Adding Data Labels via Reference Table

ed_sas_member — Mon, 04 May 2020 14:12:36 GMT

Thank you @RandoDando !

Re: Adding Data Labels via Reference Table

ballardw — Mon, 04 May 2020 14:53:16 GMT

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.