Why are you using a REPORTING procedure?  PROC TABULATE is for designed printed reports (what everyone used to mean by the word TABLE).  It is not really designed for making DATASETS. And the dataset it does produce are really only useful for making more reports, not for analysis.   If you want the results in a dataset use an ANALYSIS procedure like PROC SUMMARY instead.   If there are too many levels of ID to use it as a CLASS variable try sorting the dataset by ID first and use ID as a BY variable instead. ... View more

Hi @Tom , I think you are so brilliant at find out the major issue in answering this question, which is to add a column to be used as the group variable in proc report step, and you solved the issue very effectively and promptly, I cannot find another way better than use the n(of y:)=0; statement, so I write my version based on your ideas and this code.   However, what I would like to tell you is that, this major issue(create a group variable to be used in the proc report step and meanwhile consider the order to be displayed in the final report) perhaps is not THAT substantial and need THAT much thinking and THAT many steps/syntax to solve it (because I see in your code a lot of statements were used to deal with this issue and resulted redundant coding). Super good logic/math thinking and super good at/or remember a lot of syntax somehow becomes disadvantage here I guess😀. And what you wrote in this part (a big portion of this part) maybe not necessary (see screen capture):     As for the order of the group variable in the proc report step, the simplest way to solve it is use the order=; option. Here use order=data; makes the data to be displayed in the original order. While writing the proc report; step, I referred to SAS base 9.4 guide and see the order=; options in the 1st (or 2nd example) and I used that option (when I write proc report I always need to refer to the examples in the guide, among SAS base procs, proc report and proc tabulate probably are the steps I am least familiar with and practiced least somehow😀). ... View more

Thank you so much. Using the strip function makes it work. ... View more

Below two possible options. /* option 1 */ data want_1; if _n_=1 then do; dcl hash h1(dataset:'a'); h1.defineKey('Installation'); h1.defineData('Region'); h1.defineDone(); end; set b; _rc=h1.find(); drop _rc; run; proc print data=want_1; run; /* option 2 */ proc sort nodupkey data=a out=a_unique; by Installation; run; proc sort data=b; by Installation; run; data want_2; merge b a_unique; by Installation; run; proc print data=want_2; run; ... View more

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