BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
qwerty12
Fluorite | Level 6

code :

 

data diags;
set nicecode.diag;
run;

proc transpose data=diags out=work.d(keep=clm_no diag1-diag25) prefix=diag;
by clm_no ;
id seq_no ;
var code;
run;

 

By running this code I got below error:

slavany7_0-1670681003562.png

So I modified the code as below:

 

data diags;
set diag;
run;

proc sort data = diags;
by clm_no;
run;

proc transpose data=diags out=d(keep=clm_no diag1-diag25) prefix=diag;
id seq_no ;
var code;
run;

 

Then I got below Error:

 

slavany7_1-1670681157894.png

Here is the diag Table : 

slavany7_3-1670681390675.png

total record counts in diag table - 585546644

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

You second proc transpose code does not have the BY clm_no. Why did you remove that after sorting by that variable?

 

If you have any duplicates of ID variables when there is no BY grouping then that is expected. If you have duplicate values of ID variables within a BY group, i.e. value of Clm_no, then that would also be expected. The ID variables are used to create names for output variables. SAS will not allow a data set to have a variable name duplicated. So if the value if an ID variable, or combination of Id variables. occurs, you get this error because the requested variable names are invalid.

 

Since this is a matter of values of variables then a picture of variable types isn't particularly helpful. You would need to provide an actual example of data values.

 

Note: you can test if you might have this sort of repeated Id value but using Proc freq code:

Proc freq data=diags;
   table clm_no * seq_no /missing list;
run;

If you see a count of 2 or greater for any clm_no * seq_no combination then you need to do something else that the Transpose code you show.

 

View solution in original post

2 REPLIES 2
ger15xxhcker
Quartz | Level 8

It looks like you have duplicate values for the ID variable, seq_no, in your input dataset. You should be able to resolve this issue by using the nodupkey option on your PROC SORT step, as follows:

 

proc sort data = diags nodupkey;
by clm_no;
run;

ballardw
Super User

You second proc transpose code does not have the BY clm_no. Why did you remove that after sorting by that variable?

 

If you have any duplicates of ID variables when there is no BY grouping then that is expected. If you have duplicate values of ID variables within a BY group, i.e. value of Clm_no, then that would also be expected. The ID variables are used to create names for output variables. SAS will not allow a data set to have a variable name duplicated. So if the value if an ID variable, or combination of Id variables. occurs, you get this error because the requested variable names are invalid.

 

Since this is a matter of values of variables then a picture of variable types isn't particularly helpful. You would need to provide an actual example of data values.

 

Note: you can test if you might have this sort of repeated Id value but using Proc freq code:

Proc freq data=diags;
   table clm_no * seq_no /missing list;
run;

If you see a count of 2 or greater for any clm_no * seq_no combination then you need to do something else that the Transpose code you show.

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 1737 views
  • 0 likes
  • 3 in conversation