Help using Base SAS procedures

Proc Transpose Error - ID Value Occurs Twice in the Same Group

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

Proc Transpose Error - ID Value Occurs Twice in the Same Group

I am very close to a final solution in trying to dichotomize my words and phrases based on a listing. I am also supplying screen shots to help identify what is happening.

Below are some assorted comments and their claim numbers:

Comments.JPG

Below are the words and phrases that I am searching for:

Text4Matches.JPG

I made a numeric column associated with each of these words and phrases. The goal is to eventually get a bunch of new dichotomous variables (Boolean indicators) if the word or phrase appears in a particular comment. Originally I wanted to do it so that the actual word or phrase is appended to the end of these new "flag" variables, but that was plagued by problems. Below is sort of a shot in how the final data will look:

FinalOutputShouldLookLike.JPG

The last columnn is from my NumberForLabels column.

And the following are some of the errors I get:

Errors.JPG

My code appears below. Perhaps my proc sql routine is not making the data unique in some way? I am sure there is a simple solution to this- any and all help is highly valued and appreciated.

proc sql;
  create table scoresL as
  select CLAIMNO, COMMENTTEXT, Text4Matching, NumberForLabels
    from
    SRS_Comments500 inner join
    TextFromExcel on indexw(COMMENTTEXT, Text4Matching)>0
  order by CLAIMNO, COMMENTTEXT, Text4Matching;
quit;
proc print data=scoresL noobs; run;

proc transpose data=scoresL out=FlaggedCommentsT(drop=_Smiley Happy prefix=flag_;
  by CLAIMNO;
  id NumberForLabels;
run;
proc print data=FlaggedCommentsT noobs; run;


Accepted Solutions
Solution
‎06-03-2015 06:02 PM
Super User
Posts: 10,500

Re: Proc Transpose Error - ID Value Occurs Twice in the Same Group

It looks like you need to go back to the logic involved in assigning NumberForLabels if you are getting multiple values within CLAIMNO. Possibly the source data had the CLAIMNO split somehow so that your approach process pieces separately resulting in duplicate values for NumberForLabels with different meanings.

A crude investigative technique would be to take those values with errors and subset the data on them and sort by NumberForLabels and Claimno and see if you have multiple text values associated.

I suspect that a fix might involve and earlier SORT by CLAIMNO.

View solution in original post


All Replies
Solution
‎06-03-2015 06:02 PM
Super User
Posts: 10,500

Re: Proc Transpose Error - ID Value Occurs Twice in the Same Group

It looks like you need to go back to the logic involved in assigning NumberForLabels if you are getting multiple values within CLAIMNO. Possibly the source data had the CLAIMNO split somehow so that your approach process pieces separately resulting in duplicate values for NumberForLabels with different meanings.

A crude investigative technique would be to take those values with errors and subset the data on them and sort by NumberForLabels and Claimno and see if you have multiple text values associated.

I suspect that a fix might involve and earlier SORT by CLAIMNO.

Frequent Contributor
Posts: 115

Re: Proc Transpose Error - ID Value Occurs Twice in the Same Group

Thank you very much. It was an issue where I had text for multiple comments per single claim. This created the situation of multiple ids per by variable.

Once I stripped the text comments from the file and removed the redundant codes all is working out. Thank you again.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 2250 views
  • 0 likes
  • 2 in conversation