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-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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