Proc transpose error with same name variable and exceeds 32 length

[dennis_oz]

Hi all,

Please can you advice.

/* When I execute the below code it errors , how can I avoid the below error through code . The below data is read externally and I have no control . However , can you suggest something to overcome the above issue programmatically . */

ERROR: The ID value "'Anti smoking prdcts Freq Buy Eve'n" occurs twice in the input data set.

data foo;
infile datalines delimiter=','; 
input F1 $:100. no1 no2 ;
datalines;
Anti smoking prdcts Freq Buy Every 2 to 3 months,10,20
Anti smoking prdcts Freq Buy Every 4 to 6 months,30,40
Hair loss prev prod Freq Buy Every 2 to 3 months,40,50
Hair loss prev prod Freq Buy Every 4 to 6 months,60,70
run;

 proc transpose data=foo out=foo1; 
 id f1;
 run;

[LinusH]

That text looks more of a descriptive report header, than something suitable for a column name.

Question, why are transposing?

Perhaps you need to create a report, and do the pivoting in PROC REPORT/TABULATE etc.

Otherwise, you should clean/transform your data so you get values that are appropriate for columns names. And then use the existing text as label instead in the PROC TRANSPOSE output.

[ScottBass]

Post what you want your desired output to be, as a self-contained SAS datastep using datalines statement, posted using the "Insert SAS code" icon.

[ballardw]

---

@dennis_oz wrote:

Hi all,

Please can you advice.

/* When I execute the below code it errors , how can I avoid the below error through code . The below data is read externally and I have no control . However , can you suggest something to overcome the above issue programmatically . */

ERROR: The ID value "'Anti smoking prdcts Freq Buy Eve'n" occurs twice in the input data set.

data foo;
infile datalines delimiter=','; 
input F1 $:100. no1 no2 ;
datalines;
Anti smoking prdcts Freq Buy Every 2 to 3 months,10,20
Anti smoking prdcts Freq Buy Every 4 to 6 months,30,40
Hair loss prev prod Freq Buy Every 2 to 3 months,40,50
Hair loss prev prod Freq Buy Every 4 to 6 months,60,70
run;

 proc transpose data=foo out=foo1; 
 id f1;
 run;

---

SAS currently has a hard limit of 32 characters for a variable name. Your values are identical up to the 32d character as that is all SAS can use to make a variable name from an ID in Proc Transpose. ID values cannot be duplicated within a By group, or without at By group, not duplicated at all. So choices are 1) shorten the text 2) move the time period information to within the first 32 characters so it is included in the shortened version that will be created for variable name, 3) us MUCH shorter text and assign a long Label to the created variable, or 4) one of the report options as mentioned by others.