Hello, Am having trouble counting the number of events using proc tabulate. I wanted to keep the ID variable so I can use it to match to another dataset. Here is my code    data Have; input id $1-7 race $8-9 age_group $10-20 event_dt sex $ visit_type $ year; datalines; 5334933 W 02.1 <20 21-Apr-20 M Outpatient 2020 5790999 B 02.5 35-39 4-Jan-21 M Outpatient 2021 5848162 H 02.6 >=40 30-Aug-22 M Outpatient 2022 5956547 W 02.3 25-29 9-Jul-20 F Outpatient 2020 5917539 B 02.3 25-29 30-Jul-20 M Outpatient 2020 4319890 W 02.3 25-29 9-Jun-20 M Outpatient 2020 1002701 B 02.3 25-29 30-Sep-20 F Outpatient 2020 5825314 Z 02.6 >=40 13-Oct-20 M Outpatient 2020 7047568 O 02.3 25-29 23-Sep-20 M Outpatient 2020 7205606 B 02.2 20-24 3-Feb-22 F Outpatient 2022 ; run; Here is my code proc tabulate data=have missing out=want; class id year sex race age_group visit_type; var event_dt; table (id sex age_group), (year ALL)* N ; format sex $sex. visit_type $visit_type.; run; The processing time is taking too long so I believe something is wrong with my code. When I did not include the id variable I was able to get the count I wanted but I could not match the data to another dataset because I could not include the linkage variable id. Is there a way modify this code to include id or is there another way to count and get the same counts as  this proc tabulate. Thank you ... View more

Hello, I am unable to create subtotal for each group. I am getting this error.  ERROR: You can only BREAK on GROUPing and ORDERing variables.  When I adjusted by code and include group or order in the define,  i still have the error. This is my output. I want subtotal under each group (Sex, Age category, Race/Ethnicity) this is my code proc report data=cases8 style(report)=[background=white] style(header)=[background=#919CB2 foreground=black borderstyle=hidden]; col Cat2 Y2020 Y2021 Y2022 Y2023 Y2024 Y3000; define cat2/order display 'Variable'; define Y2020/analysis sum 'Year 2020'; define Y2021/analysis sum 'Year 2021'; define Y2022/analysis sum 'Year 2022'; define Y2023/analysis sum 'Year 2023'; define Y2024/analysis sum 'Year 2024'; define Y3000/analysis sum 'Total'; /*Make row headings bold and more readable*/ break after cat2/summarize; rbreak after /summarize; compute after; cat2='SubTotal'; endcomp; compute cat2; if strip(cat2) in('Sex' 'Age category' 'Race/Ethnicity') then do; call define(_row_, "style", "style=[backgroundcolor=#DFE5E5 fontweight=bold]"); end; endcomp; title "Total number of encounters"; run; ... View more

Hello, I would like to merge 2 dataset. data A has a value for my region= RRE, AAA, OTHER etc and data B  has region = RRE, AAA etc.  Data B does NOT have the OTHER but data A has both OTHER and the correct value.  After merging, I want my final data to have only the correct value from data B instead of the OTHER from data A.  data A; input Region $ 1-5 Installation $; datalines; RRE USS OTHER USS OTHER USS AAA USTU AAA USTU OTHER USS OTHER USTU OTHER UNSP OTHER UNSP OTHER UNSP ; run; data B ; input Region $ 1-5 Installation $; datalines; RRE USS RRE USS RRE USS AAA USTU AAA USTU AAA USTU OTHER UNSP OTHER UNSP OTHER UNSP ; run; output data C RRE   USS RRE  USS RRE  USS RRE  USS AAA    USTU AAA      USTU AAA      USTU OTHER UNSP OTHER UNSP OTHER UNSP My code is not giving the expected output. I also used the line which I commented out but still did not get the expected output C data C; merge A (in=in1 ) B (in=in2) if in2 then REGION = in1; *if REGION = 'OTHER' and installation ne 'UNSP' then REGION = 'in2'; run;   ... View more

Hello, I want to use array to find unique icd code in a dataset. The first code is an icd diagnoses code and the second is a procedure code. My dataset has millions of record. The diagnosis code is from 1-12 and the procedure code is from 1-25. For the purposes of the example , i used 1-2 for diagnosis and 1-3 for procedure code.  I would like to find any records with either one or multiple codes in the encounter so I can subset to only icd codes of interest. My code below is not doing what I want. Thanks in advance. /* Sample data lines */ data have; input patient_id $ proc1 $ proc2 $ diag1 $ diag2 $; datalines; 001 A0H7XJZ B4JZZZZ F1020 Z864 002 H0ZXPZZ K4MZZZZ E669 Z733 003 C02DXZZ D0Y0KZZ J45909 F419 ; data medical_records; set have; array proc_code[2] $7 proc1-proc2; array diag_code[3] $10 diag1-diag2; do i = 1 to 2; if proc_code [i] in ("H0ZXPZZ", "D0Y0KZZ") then valid_proc=1; else valid_proc=0; end; do i = 1 to 3; if diag_code [i] in ("F1020") then valid_diag=1; else valid_diag =0; end; drop i; run; Expected outcome Patient_id proc1 proc2 diag1 diag2 diag3 valid_proc valid_diag 1 A0H7XJZ B4JZZZZ F1020 Z864 Z125 0 1 2 H0ZXPZZ K4MZZZZ E669 Z733 J492 1 0 3 C02DXZZ D0Y0KZZ J45909 F419 F1020 1 1       ... View more

Hello, Please how can I keep the first_date and last date on the same row. Here is my code but didn't work. Thanks data have; input ID $6. event_dt First_dt Last_dt ; format event_dt First_dt Last_dt date9.; informat event_dt First_dt Last_dt date9.; datalines; 011396 14SEP2023 . 14SEP2023 011396 02AUG2023 . . 011396 18AUG2021 18AUG2021 . 011396 23JUN2023 . . 034627 01DEC2009 01DEC2009 . 034627 24JUN2023 . 24JUN2023 034627 11DEC2019 . . 011427 11SEP2010 . . 011427 09AUG2010 09AUG2010 . 011427 11SEP2012 . . 011427 11JUN2013 . 11JUN2013 ; run; proc sort data=have; by id event_dt;run; data want; set have; by id; format event_dt First_dt Last_dt date9.; if First_dt ne . or Last_dt ne . then last_date=Last_dt; run; Desired output ID event_dt First_dt Last_date 011396 18AUG2021 18AUG2021 14SEP2023 034627 01DEC2009 01DEC2009 24JUN2023 011427 09AUG2010 09AUG2010 11JUN2013 ... View more

Hello, I am importing excel sheets into SAS but values with leading zero are missing when imported. How can I resolve this issue.  %macro dataload(sheet); proc import out=work.&sheet datafile="S:\document\inpatient.xlsx" dbms=xlsx replace; sheet="&sheet"; getnames=yes; run; %mend dataload; %dataload(cbcs); %dataload(vhi); For example, In excel 034567786 when imported will be 34567786. I can use data step  infile statement to import the file but i have 2 sheets. How can I import each sheets using the infile statement or how can I manipulate the proc import statement so that it keeps the leading zeros? Thanks  ... View more

Hello, I have a multiple claims from 2016 to 2021. I want to find the total records by (patid) in all the claims without counting any patid twice so I can find the descriptive analysis. For example, In 2016, I want patid=2, In 2017, patid=3, In 2018,patid=3, In 2019, patid=4, In 2020, patid=5.  For the Total N, I dont want to count a patid twice so Total N=5 for patid2016-2021 I am not sure how to do this in SAS. Please help. Thanks Data Test2016; input patid $ clmid $ dtstart :YYMMDD10.; format dtstart YYMMDD10. ; datalines; 001 abc 2017-01-01 001 def 2017-02-01 001 ghi 2017-05-01 002 xvu 2018-01-01 002 cde 2018-02-20 ; run; Data Test2017; input patid $ clmid $ dtstart :YYMMDD10.; format dtstart YYMMDD10. ; datalines; 001 abc 2017-01-01 001 def 2017-02-01 001 ghi 2017-05-01 002 xvu 2018-01-01 002 cde 2018-02-20 003 adt 2020-03-25 003 tyh 2021-01-15 ; run; Data Test2018; input patid $ clmid $ dtstart :YYMMDD10.; format dtstart YYMMDD10. ; datalines; 001 abc 2017-01-01 001 def 2017-02-01 001 ghi 2017-05-01 002 xvu 2018-01-01 002 cde 2018-02-20 003 adt 2020-03-25 003 tyh 2021-01-15 ; run; Data Test2019; input patid $ clmid $ dtstart :YYMMDD10.; format dtstart YYMMDD10. ; datalines; 001 abc 2017-01-01 001 def 2017-02-01 001 ghi 2017-05-01 002 xvu 2018-01-01 002 cde 2018-02-20 003 adt 2020-03-25 003 tyh 2021-01-15 004 wse 2020-03-25 004 def 2021-01-15 ; run; Data Test2020; input patid $ clmid $ dtstart :YYMMDD10.; format dtstart YYMMDD10. ; datalines; 001 abc 2017-01-01 001 def 2017-02-01 001 ghi 2017-05-01 002 xvu 2018-01-01 002 cde 2018-02-20 003 adt 2020-03-25 003 tyh 2021-01-15 004 wse 2020-03-25 004 def 2021-01-15 005 aaa 2020-03-25 005 bbb 2021-01-15 ; run;   ... View more