topic Re: Need help in grouping observations and assigning numbers to each group in SAS Programming

Need to assign one number to multiple observations based on a incremental but resetting variable

mosabbirfardin — Tue, 21 May 2019 17:32:06 GMT

Hello all,

I am using SAS base 9.4 M5 on enterprise guide 7.15 HF7. I have a HL7 file where I am getting multiple observations for diagnosis with incremental Set_ID for one person. I want to group all these observations per person and assign numbers so that I can de-normalize the data. The maximum number of observations per person(visit) is 12 in my source file.

Input dataset:

Segment_Type_ID Set_ID_DG1 Diagnosis_Coding_Method Diagnosis_Code
DG1 1 ICD E559
DG1 2 ICD E039
DG1 3 ICD I10
DG1 4 ICD R7301
DG1 5 ICD R5383
DG1 1 ICD R079
DG1 1 ICD E782
DG1 2 ICD Z6841
DG1 3 ICD I10
DG1 4 ICD Z125

I want the output to be like this:

Segment_Type_ID Set_ID_DG1 Diagnosis_Coding_Method Diagnosis_Code visit_no
DG1 1 ICD E559 1
DG1 2 ICD E039 1
DG1 3 ICD I10 1
DG1 4 ICD R7301 1
DG1 5 ICD R5383 1
DG1 1 ICD R079 2
DG1 1 ICD E782 3
DG1 2 ICD Z6841 3
DG1 3 ICD I10 3
DG1 4 ICD Z125 3

I have used the following code but failed to get the desired output as it fails to capture maximum value per visit in the Set_ID_DG1 variable:

data DG1_want;
set WORK.DG1_have;
retain count_up_by_12 rowcount 1;
if first.Set_ID_DG1 then do;
count_up_by_12=1;
rowcount=1;
end;
else rowcount+1;
output;
if mod(rowcount,12)=1 then count_up_by_12+1;
drop rowcount;
run;

Please, any suggestions ? Thanks in advance.

Need help in grouping observations and assigning numbers to each group

mosabbirfardin — Tue, 21 May 2019 18:11:31 GMT

Hello all,

Input dataset:

I want the output to be like this:

I have used the following code but failed to get the desired output as it fails to capture maximum value per visit in the Set_ID_DG1 variable:

data DG1_want;
set WORK.DG1_have;
retain count_up_by_12 rowcount 1;
if first.Set_ID_DG1 then do;
count_up_by_12=1;
rowcount=1;
end;
else rowcount+1;
output;
if mod(rowcount,12)=1 then count_up_by_12+1;
drop rowcount;
run;

Please, any suggestions ? Thanks in advance.

Re: Need help in grouping observations and assigning numbers to each group

novinosrin — Tue, 21 May 2019 18:21:55 GMT

Hi @mosabbirfardin

Are you asking for

data DG1_want;
set WORK.DG1_have;
if set_id_dg1=1 then visit_no+1;
run;

Re: Need to assign one number to multiple observations based on a incremental but resetting variable

Reeza — Tue, 21 May 2019 18:21:28 GMT

I think you're overthinking this. If this doesn't work, please expand your sample data to include cases where it fails.

data want;
set have;
by segment_type_id notsorted;

retain visit_no;

if first.segment_type_id then visit_no = 0;

if set_id_dg1 = 1 then visit_no+1;

run;

@mosabbirfardin wrote:

Hello all,

I am using SAS base 9.4 M5 on enterprise guide 7.15 HF7. I have a HL7 file where I am getting multiple observations for diagnosis with incremental Set_ID for one person. I want to group all these observations per person and assign numbers so that I can de-normalize the data. The maximum number of observations per person(visit) is 12 in my source file.

Input dataset:

Segment_Type_ID Set_ID_DG1 Diagnosis_Coding_Method Diagnosis_Code
DG1 1 ICD E559
DG1 2 ICD E039
DG1 3 ICD I10
DG1 4 ICD R7301
DG1 5 ICD R5383
DG1 1 ICD R079
DG1 1 ICD E782
DG1 2 ICD Z6841
DG1 3 ICD I10
DG1 4 ICD Z125

I want the output to be like this:

Segment_Type_ID Set_ID_DG1 Diagnosis_Coding_Method Diagnosis_Code visit_no
DG1 1 ICD E559 1
DG1 2 ICD E039 1
DG1 3 ICD I10 1
DG1 4 ICD R7301 1
DG1 5 ICD R5383 1
DG1 1 ICD R079 2
DG1 1 ICD E782 3
DG1 2 ICD Z6841 3
DG1 3 ICD I10 3
DG1 4 ICD Z125 3

I have used the following code but failed to get the desired output as it fails to capture maximum value per visit in the Set_ID_DG1 variable:
data DG1_want;
set WORK.DG1_have;
retain count_up_by_12 rowcount 1;
if first.Set_ID_DG1 then do;
count_up_by_12=1;
rowcount=1;
end;
else rowcount+1;
output;
if mod(rowcount,12)=1 then count_up_by_12+1;
drop rowcount;
run;
Please, any suggestions ? Thanks in advance.

Re: Need help in grouping observations and assigning numbers to each group

Reeza — Tue, 21 May 2019 18:23:07 GMT

Please don't post the same question twice, I've merged them into one for you here.

Re: Need help in grouping observations and assigning numbers to each group

novinosrin — Tue, 21 May 2019 18:26:48 GMT

@mosabbirfardin Or more complete


data DG1_want;
set WORK.DG1_have;
by Segment_Type_ID     ;
if first.Segment_Type_ID     then visit_no=1;
else if  set_id_dg1=1 then visit_no+1;
run;

Re: Need help in grouping observations and assigning numbers to each group

mosabbirfardin — Tue, 21 May 2019 18:26:31 GMT

Thanks a lot. It worked perfectly!!