It is just a querying issue. You could get this by PROC FORMAT or Hash Table. 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 _proc; input _proc $; cards; H0ZXPZZ D0Y0KZZ ; data _diag; input _diag $; cards; F1020 ; run; data want; if _n_=1 then do; if 0 then set _proc _diag; declare hash p(dataset:'_proc'); p.definekey('_proc'); p.definedone(); declare hash d(dataset:'_diag'); d.definekey('_diag'); d.definedone(); end; set have; array _p{*} proc:; array _d{*} diag:; valid_proc=0; valid_diag =0; do i=1 to dim(_p); if p.check(key:_p{i})=0 then valid_proc=1; end; do j=1 to dim(_d); if d.check(key:_d{j})=0 then valid_diag=1; end; drop _: i j; run; ... View more

Here's a variation that gives you all the data you might ever want ... but lets you postpone the typing of all the variable names until you know the numbers you want.  There is no way to avoid naming the variables you want however.  Apologies for stealing bits of code here and there from earlier posts. proc summary data=have nway; class id; var _numeric_; output out=minimums min=; output out=maxiimums max=; run; This gives you two new data sets (I'll describe just one).  MINIMUMS contains one observation per ID, with the smallest value of each numeric variable.  You don't have to name any of them, because this program re-uses the same variable name.  In MINIMUMS, there is one observation per ID, and EVENT_DT holds the minimum value of EVENT_DT from the original data.  So the same variable name gets re-used without any typing or inventing names on your part.   You can always decide later which of the output pieces you want to use in your report.  The price you pay for this simplicity is on the back end when it is more complex to select the pieces that you want.  Here are the hoops you might have to jump through when using the data: data want; merge have minimums (keep=id event_dt rename=(event_dt=firstdt)) maximums (keep=id event_dt rename=(event_dt=lastdt)); by id; run; You start with simplicity, as well as great flexibility.  But you can't totally get around your responsibility to name the variables you want to use. ... View more

You can import these columns/variables of Excel as CHARACTER variables to retain those leading zero. 1) the first way is using PROC IMPORT: proc import datafile = "c:\temp\date.xlsx" out =have replace dbms =excel ; dbdsopts="dbsastype=(age='numeric' weight='char(20)' )"; run; 2) the second way is using LIBNAME: libname x excel 'c:\temp\date.xlsx'; data have2; set x.'date$'n(dbsastype=(age='numeric' weight='char(20)')); run; ... View more

Do you want them counted by the year that is implied by the NAME of the dataset?   Assuming the dataset are already sorted by PATID you could do this: data counts; if eof then output; merge test2016(in=flag2016) test2017(in=flag2017) test2018(in=flag2018) test2019(in=flag2019) test2020(in=flag2020) end=eof ; by patid; if first.patid; countall+1; array flags [2016:2020] flag: ; array counts[2016:2020] count2016-count2020; do year=2016 to 2020; counts[year]+flags[year]; end; keep count: ; run; Results Obs countall count2016 count2017 count2018 count2019 count2020 1 5 2 3 3 4 5 Or the YEAR that is implied by the DATE variable in the dataset?  Then use SET instead of MERGE and add DTSTART to the BY statement to find their first date. data counts; if eof then output; set test2016-test2020 end=eof; by patid dtstart; countall+first.patid; array counts[2016:2021] count2016-count2021 (6*0); retain count:; if first.patid or year(dtstart) ne lag(year(dtstart)) then counts[year(dtstart)]+1 ; keep count: ; run; Result Obs countall count2016 count2017 count2018 count2019 count2020 count2021 1 5 0 1 1 0 3 3   ... View more

@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