transposed error

[Dhana18]

Good morning,

i am stocked with this problem, could you please help me?

ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=10357151
ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=10358360
WARNING: 2 BY groups omitted due to earlier errors.

[Amir]

Hi,

Please post a copy of the log that includes the SAS code along with error / note messages.

Regards,

Amir.

[Dhana18]

ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=0357151
ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=0358360
WARNING: 2 BY groups omitted due to earlier errors.

[Kurt_Bremser]

Maxim 3, know your data.

Within a by group, values in the id variable must not appear more than once. Determine what to do with those values before you transpose.

[Reeza · Accepted solution]

Your BY group has duplicate entries. You usually have three options:

1. Remove duplicates

2. Consolidate entries - add a count or sum variables

3. Add a variable to your BY or ID statement that would uniquely identify each entry.

---

@Dhana18 wrote:

Good morning,

i am stocked with this problem, could you please help me?

ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=10357151
ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=10358360
WARNING: 2 BY groups omitted due to earlier errors.

---

[novinosrin]

Please show us your data sample, your required output, your code and the log

[ballardw]

The procedure option LET on the proc statement will allow multiple values for an ID variable. However the results are typically not what you want.

Example data and desired output may help resolve your actual issue.

[Dhana18]

Still having the same problem, spent all day today figuring this out,please help.

ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=0210357151
ERROR: The ID value "HIV_COMBO" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
EVENT_ID=0210358360
WARNING: 2 BY groups omitted due to earlier errors.

[novinosrin]

We requested

"Example data and desired output may help resolve your actual issue."

[Dhana18]

My data is very complex with thousands of records.  same person can visit the clinic many times in a year and at one visit the person can have many different tests.

for example

data test;
input patient_id event_id test site $ results$;
DATALINES;
1 01 300 U N 
1 01 700 O P
1 01 700 P N
1 01 900 V N
1 01 700 R P
2 02 300 V P
2 01 900 B P
;
RUN;

For example, I want to find out  any 700 tests in a visit / event in a given period of time with p or n result.

[Reeza]

Ok, is that your input? What would you expect as output, exactly?

If this does not reflect the situations you were referring to and generating the errors, please modify your input data to ensure the issues you're encountering are in the sample data. 

To answer the question you posed is relatively trivial, except you mention a given period but don't actually have any time/date variable in your data. See the code below, which assumes a date variable. The output is also in the data set want.

---

@Dhana18 wrote:

For example, I want to find out  any 700 tests in a visit / event in a given period of time with p or n result.

---

proc freq data=test;
where test=700 and results in ('P', 'N');
*assume you have some date variable;
table date_event / out=want;
format date_event yymmn6.;
run;

[Dhana18]

Thank you Reza,

Here is sample data. i would like to count each group of test with result for example  NAAT ONLY (NAATR,NAATP, NAATP, NAATG), CULTURE ONLY (CULP,CULG, CULR) and BOTH NAAT and CULTURE and EITHER NAAT or CULTURE.  what logic should i use?

EVENT_ID

HIV_GEENIO

HIV_COMBO

PMNS

GNID

GNED

HCG

CULP

CULG

CULR

NAATU

NAATP

NAATG

NAATR

44

P

P

N

P

P

46

N

N

N

47

N

N

N

N

51

N

P

N

N

N

N

52

N

N

N

53

N

N

55

N

N

N

N

N

56

N

N

57

N

N

N

N

N

N

58

N

N

N

N

60

N

N

N

N

N

61

N

N

62

N

P

N

N

N

N

64

N

N

65

N

N

66

N

P

N

N

N

67

N

N

N

N

68

N

N

N

N

70

N

P

N

72

N

P

N

N

N

73

N

N

N

[Dhana18]

Hi,

I have this sample data. I would like to get each group of test for example NAAT only CULT only Both NAAT and CULTURE, either CULTURES or NAAT. what logic should i use?

EVENTID HIV PMNS CULP CULG CULR NAATR NAATP NAATU NAATG

44            N       P           N       N           N         P         N           P           N

45                     N                     N           P          N         N          N

46            N                      N                                N                     P           N

47                      N                                   N                                        

48           N                       N                    N            N           N                 P

[Reeza]

What do the P/N mean? Do they affect the results?

Calculate two more indicator variables and then use those instead.

CUL=0; NAAT=0;
if missing(coalescec(culp, culg, culr)) then CUL=1;
if missing(coalescec(NAATR, NAATP, NAATU, NAATG)) then NAAT=0;

*optional;
CUL_OR_NAAT = max(CUL, NAAT);
CUL_AND_NAAT = CUL*NAAT;

And then the summaries once those variables are created:

proc means data=have N MEAN SUM;
var CUL NAAT CUL_OR_NAAT CUL_AND_NAAT;
ods output summary=want
run;

---

@Dhana18 wrote:

Hi,

I have this sample data. I would like to get each group of test for example NAAT only CULT only Both NAAT and CULTURE, either CULTURES or NAAT. what logic should i use?

EVENTID HIV PMNS CULP CULG CULR NAATR NAATP NAATU NAATG

44            N       P           N       N           N         P         N           P           N

45                     N                     N           P          N         N          N

46            N                      N                                N                     P           N

47                      N                                   N                                        

48           N                       N                    N            N           N                 P

---

[Kurt_Bremser]

And the solution is also still the same: follow the advice @Reeza gave, and which you marked as the solution.