topic Re: transposed error in SAS Programming

transposed error

Dhana18 — Fri, 31 Aug 2018 14:22:05 GMT

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.

Re: transposed error

Amir — Fri, 31 Aug 2018 14:35:40 GMT

Hi,

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

Regards,

Amir.

Re: transposed error

Kurt_Bremser — Fri, 31 Aug 2018 14:36:10 GMT

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.

Re: transposed error

Dhana18 — Fri, 31 Aug 2018 14:38:31 GMT

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.

Re: transposed error

Reeza — Fri, 31 Aug 2018 14:39:01 GMT

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.

Re: transposed error

novinosrin — Fri, 31 Aug 2018 14:51:29 GMT

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

Re: transposed error

ballardw — Fri, 31 Aug 2018 16:23:45 GMT

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.

Re: transposed error

Dhana18 — Wed, 05 Sep 2018 19:39:39 GMT

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.

Re: transposed error

novinosrin — Wed, 05 Sep 2018 19:43:08 GMT

We requested

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

Re: transposed error

Kurt_Bremser — Wed, 05 Sep 2018 19:56:18 GMT

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

Re: transposed error

Dhana18 — Wed, 05 Sep 2018 21:07:34 GMT

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.

Re: transposed error

Reeza — Wed, 05 Sep 2018 21:12:22 GMT

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;

Re: transposed error

Dhana18 — Thu, 06 Sep 2018 00:50:27 GMT

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

Re: transposed error

Dhana18 — Thu, 06 Sep 2018 01:21:02 GMT

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

Re: transposed error

Reeza — Thu, 06 Sep 2018 02:00:28 GMT

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