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.
Are you asking for
data DG1_want;
set WORK.DG1_have;
if set_id_dg1=1 then visit_no+1;
run;
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.
Are you asking for
data DG1_want;
set WORK.DG1_have;
if set_id_dg1=1 then visit_no+1;
run;
@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;
Thanks a lot. It worked perfectly!!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.