BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ewillisva
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 @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;

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 @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;

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

ewillisva
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 2024

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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