@Tom @PaigeMiller @Patrick @Kurt_Bremser @Astounding  THANK YOU ALL!!! I appreciate all your comments and guidance. All of you are right. If I could pick multiple solutions I would i pick all but SAS only allows one solution so I just pick the one I found simple to understand - knowing that i continue to learn SAS, am not an expert yet. Thank you all again.  ... View more

Maxim 19: Long Beats Wide. In 99 % of cases encountered here on the communities, transposing from long to wide is not necessary and in fact detrimental to the task at hand. Why do you think you need the wide structure? ... View more

@CathyVI From the looks of it the solution from this discussion could also be applicable to your case. ... View more

data SDpost; set a; by id; length PostStroke_S PostStroke_D $20; /* Initialize variables */ PostStroke_S = ' '; PostStroke_D = ' '; /* Logic for Seizure */ if First_Seizure ne . then do; /* Check if seizure occurred before any stroke event */ if (not missing(First_Ischemic) and First_Seizure < First_Ischemic) or (not missing(First_Hemorrhagic) and First_Seizure < First_Hemorrhagic) then PostStroke_S = 'No'; else PostStroke_S = 'Yes'; end; /* Logic for Dementia */ if First_Dementia ne . then do; /* Check if dementia occurred before any stroke event */ if (not missing(First_Ischemic) and First_Dementia < First_Ischemic) or (not missing(First_Hemorrhagic) and First_Dementia < First_Hemorrhagic) or (not missing(R_Dementia_12) and R_Dementia_12 < First_Ischemic) or (not missing(R_Dementia_12) and R_Dementia_12 < First_Hemorrhagic) then PostStroke_D = 'No'; else PostStroke_D = 'Yes'; end; run; ... View more

@CathyVI  - I suggest you first try defining a LIBNAME for one folder. Does that work? You can concatenate two existing libraries into a third one: libname L1 '/Folder1'; libname L2 '/Folder2'; libname L3 (L1 L2);   Also, there was an error in your example that I didn't spot until now (remove comma between the paths): %let path= ('/_ABC_CDB_SASDS/CCW/2006' '/_ABC_CDB_SASDS/CCW/2007'); libname ccw &path; ... View more

This is the sort of thing PROC SUMMARY does, in a single pass of the data, with no need to sort:   proc summary data=have nway ; class ID drug_name; var prescrpt_dt; output out=want (drop=_type_) max=max_dt; run;   ... View more

Pre and post event analysis can be done without transposing the data.  Can you be more specific about exactly what pre and post event analyses you will be doing? ... View more

You're moving the goal posts.  😞 There's no timing in your output, and you're transforming a long data set into a wide one with no obvious logic. I have no idea what you want. Do you?   ... View more

@ballardw  Thank you. Your coding is very helpful. Now I would like to identify "Pre" and "Post first stroke"  for AFIB and ONLY "Post Stroke" for Seizure. I will put my code in </> moving forward.    You say " the initial/first ever cancer diagnosis (Ischemic Stroke or Hemorrhagic Stroke)", which is it Cancer or stroke? You don't provide any actual details to identify cancer and I know from family history that Ischemic Stroke is not cancer. Answer:I mean initial stroke NOT cancer.   Your repeatedly say "create a label". Does this mean a variable with the name Label? (which has issues around records that would need multiple 'label's) Or create a variable named "Recurrent", for example with some value? Which means we need to know the value you want. Answer: I want to create a variable named "Recurrent", with 1 or 0.   You seem to have some of the ICD 9 and 10 codes listed of interest (example AFIB and Seizure) but don't have ICD 10 for the others. So are the lists actually complete? Answer: There are multiple icd codes per record but I am only interested in AFIB & seizure. For example 1 patient may have dx of AFIB, stroke , eye infection but am only interested in the AFIB and stroke.  You also have the same ICD codes in multiple descriptions Ischemic Stroke, Hemorrhagic Stroke and Recurrent Ischemic Stroke. Are we supposed to have some additional rules as to which is which or is this just a not very clear description of some use of the ICD code. Answer: Same code should be use as initial and recurrent Ischemic Stroke or Hemorrhagic Stroke, the rule that applies is first icd will be the "initial" and  other repeated code will be "recurrent".   So can you provide an example of what you expect the output to look like for at least one ID with AFIB and one with seizure? Answer:  I would like to have variables "Pre" and "Post"  initial stroke depending on the encounter date and initial stroke date. Id diagnosis_dt _label_ Icd1 icd_text   First_IS First_HS First_AFIB Recurrent AFIB Pre- first stroke Post-first stroke 0987234 08MAY2003 dx1 42731 AFIB         1   1   0987234 30JUL2006 dx1 4321 Hemorr Stroke . 1 .   .   0987234 14AUG2006 dx2 42731 AFIB . . . 1 . 1       ... View more

I have no clue what a PHA might be but if TOTAL is easily derived using the values in PHA and those other codes then it maybe should not be in this set. SAS Reporting tools such as Proc Tabulate, Report and even Print can sum values and Tabulate and Report can do other summaries usually considered as "Total". ... View more

In an earlier post in this thread you wrote: I am not addition another variable or row after this. And now you are requesting just that: @CathyVI wrote: if you can add an ID variable to the data and some will have 2 records, this will be nice.  You have also opened a second thread find the count of variable by group where, as far as I see, the same problem is being discussed.   In this situation I am not going to give another precise shot at a moving target, sorry, but suggest a general four-step approach: Aggregate the data to one observation per ID. Classify the aggregated observations into the categories of interest. Count the observations per category. Create a report from the counts. Example solutions for steps 2 (before aggregation, though) through 4 have been presented in this thread. Whether a DATA step, PROC SQL, PROC SUMMARY or other techniques are most appropriate for step 1 depends on the data structure and on the aggregation rules, neither of which we have seen entirely. For instance, if two records provide different pieces of information about forms that have been filled, their (e.g. chronological) order may or may not be relevant. ... View more

@CathyVI wrote: @Patrick  How can anyone with a value of 1 be counted as a single case. For example row 33 in our data. When form2=2,case32=0 and event46=1, the report_form_name should be Event46A instead of multiple.    Anyone with 2 double digits e.g., 110, 220 should remain as multiple.   So now it's about counting cases and no more forms which totally changes the story! How would you want to report cases 112, 121, 211, 122, 212, 221? Please be specific and define all your cases. Provide both the variable they should appear under AND the time slot if there are multiple.   It would help if you could provide below table fully populated. With this data one can create formats which then should make the rest of the coding rather simple. Multiple patterns can of course belong to the same case (case_no). It would then also help if you provide sample data for all the patterns. Obs pattern case_no time_slot description 1 000       2 001       3 002       4 010       5 011       6 012       7 020       8 021       9 022       10 100       11 101       12 102       13 110       14 111       15 112       16 120       17 121       18 122       19 200       20 201       21 202       22 210       23 211       24 212       25 220       26 221       27 222         ... View more

@CathyVI wrote: @PaigeMiller  Yes. I want to get the variables in the order for the report. This is the report table.     Just to quickly ask this here @ballardw How do I make my 'Total' to be placed at the top just as it is in this report table with pro report.  Thanks @PaigeMiller @ballardw    N % Total   100 Sex     Male     Female     Age category     <20     20-24     25-29     30-34     35-39     40-44     45-49     50+     Race/Ethnicity     Non-Hispanic White     Non-Hispanic Black     Hispanic     Other     If your Total is computed from the values in the data then with Report that would be a RBREAK BEFORE; If you are forcing a column variable that has the value of total and associated in the data set then I think you are looking either adding an order variable that appears first in your Columns statement and has the NOPRINT options in the define along with order or a custom format and the Preloadfmt option. But from what you've said your data isn't ready. ... View more

Did you try the solutions I posted? That should solve your problem if you're sticking with this methodology. ... View more