BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RandoDando
Pyrite | Level 9

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 IDInspection Defect NameDefect CodeCount
AB123000983750Clogged Drain - Bathroom375932
AB123000983750Damaged Wall4820953
AB123000983750Mold/Mildew4394571

...

 

Here is the transposed table (as example):

Inspectioncnt37593482095439457
983750obs_cnt231

...

 

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?  

 

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

Capture d’écran 2020-05-04 à 15.40.58.png

View solution in original post

4 REPLIES 4
ed_sas_member
Meteorite | Level 14

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;

Capture d’écran 2020-05-04 à 15.40.58.png

RandoDando
Pyrite | Level 9
That worked. Hooray for simple solutions! Thanks
ballardw
Super User

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.

sas-innovate-white.png

Missed SAS Innovate in Orlando?

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.

 

Register now

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1109 views
  • 0 likes
  • 3 in conversation