Best trick is to ask your hierachical data provider to send you directly the xml files and do a custom maping yourself. At least given the fact that most hierachical data is at one point or another stored as xml. It is very easy to self learn the basics of xml mapping ... View more

I don't think the data set has a record for each level of parent for any given child. You would have to somehow preprocess the data to create a record with a level1 child and his level 3/4/5/6 parents to appropriately use a transpose/by combination. At least so it felt with the data sample ... View more

Not necessarily the simplest approach but here's how I'd go about it. data want;      if _n_=1 then do;      declare hash hl2(dataset: "have(where=(level=2) rename=(child=nextchild))");      hl2.defineKey('parent');      hl2.defineData('nextchild');      hl2.defineDone();      declare hash hl3(dataset: "have(where=(level=3) rename=(child=nextchild))");      hl3.defineKey('parent');      hl3.defineData('nextchild');      hl3.defineDone();      declare hash hl4(dataset: "have(where=(level=4) rename=(child=nextchild))");      hl4.defineKey('parent');      hl4.defineData('nextchild');      hl4.defineDone();      declare hash hl5(dataset: "have(where=(level=5) rename=(child=nextchild))");      hl5.defineKey('parent');      hl5.defineData('nextchild');      hl5.defineDone();      declare hash hl6(dataset: "have(where=(level=6) rename=(child=nextchild))");      hl6.defineKey('parent');      hl6.defineData('nextchild');      hl6.defineDone();      end;      set have(where=(level=1));      rc=hl2.find(key: child);      if rc=0 then do;           parent1=nextchild;           rc=hl3.find(key: nextchild);           if rc=0 then do;                parent2=nextchild;                rc=hl4.find(key: nextchild);                if rc=0 then do;                     parent3=nextchild;                     rc=hl5.find(key: nextchild);                     if rc=0 then do;                          parent4=nextchild;                          rc=hl6.find(key: nextchild);                          if rc=0 then parent5=nextchild;                     end;                 end;           end;      end;      drop nextchild parent level rc; run; Based on your data example, I assumed there were 6 levels but since level1 doesn't have a parent, that there should only be parent1-parent5 and not parent-parent6 variables to fill. If there are 7 levels or if level1 can have both a parent and a child it can be worked around. The issue with the above approach is that if any "key" is present multiple times (like if xxx has parents yyy and zzz) then the hash table, defined as is won't support it and will retain only one of the two (I can't remember if it retains first key or replaces). It can be worked around using multidata option but then the looping becomes a tad bit hectic. Point being, conceptually this does a merge whilst renaming variables appropriately of the following subsets of your original data: have(where(level=1)) have(where(level=2)) ... have(where(level=6)) Using this successive approach with the above data subsets and renaming the variable should also allow you to provide the appropriate results without messing with multidata hash tables like below: proc sql;      select t1.child, t2.parent1, t3.parent2, t4.parent3, t5.parent4, t6.parent5      from      have(where=(level=1) drop=parent) as t1      left join      have(where=(level=2) rename=(parent=child child=parent1))      on t1.child=t2.child      left join      have(where=(level=3) rename=(parent=parent1 child=parent2)) as t3      on t2.parent1=t3.parent1      left join      have(where=(level=4) rename=(parent=parent2 child=parent3)) as t4      on t3.parent2=t4.parent2      left join      have(where=(level=5) rename=(parent=parent3 child=parent4)) as t5      on t4.parent3=t5.parent3      left join      have(where=(level=6) rename=(parent=parent4 child=parent5)) as t6      on t5.parent4=t6.parent4 ; quit; Note this is all untested its more of a logical guideline on how you can tackle the problem. Vince *Edit indeed left join seemed more appropriate for the OPs objectives so I edited the sql example ... View more

Sigh, sorry, updated my reply again. defineDone is a method of the hash table and SAS requires () syntax at the end h.defineDone(); I always omit it by mistake when I don't have data to test my syntax. Edit: If this can be of any additionnal diagnosis of your original post code. Since you are not keeping a new variable  for your MBR_SYS_ID each time you find a major study case, your do pt=_N_+1 ... end; statement loops on all studies content that is sorted below your current mbr_sys_id so if some studies overlap, each major study will also cause other studies' procedures that occured within the appropriate timeframe to be output including even the record for a new study if it has an associated procedure applied that day. ... View more

my bad, it's < (dataset: and not (datasets: updating original post. ... View more

Hi Ashok, I'm not going to lie here I'm not quite familiar with proc report. My guess however, based on the example in the second link is that if you were to set one as 50% and the total width as 100% in your proc report statement, that the leftover portion would be automatically adjusted to whatever is leftover - likely "almost 50% but there are fractions of % taken for borders and whatnot" I suggest you try removing the width option from either define statement and if it doesn't fix it, I can try and dig more into this issue. Vince ... View more

I think $varyingw. is strictly an informat found SAS(R) 9.4 Formats and Informats: Reference but you can lookup for it in the format list it is not there. It does make some sense too. Depending on your ODS destination, you *should* be able to avoid the issue of white space padding by using a trim(measure_name). I am not very familiar with proc report so I may be wrong about line statement supporting trim() like the put statement since there are a few features of put not supported by line Vince ... View more

proc format;      value $CMC           'pcifu182'='1-182 DAY PCI REDO RATE'           'pcifu30'='1-30 DAY PCI REDO RATE'           /* more formats to cover all distinct procedure */           ; run; proc transpose data=have out=want;      by strata procedure;      id location;      var value_display; run; ods rtf file='c:\somefile.rtf'; proc print data=want;      by strata;      format procedure $CMC.; run; ods rtf close; The syntax is only an indication of how to go I can't test this from home at all. If you have an existing dataset with the full procedure name / procedure acronym you could/should use that instead of the proc format just to avoid hard coding the proc format for all your different procedures. If you want to change the name of the variables created by transpose (IP OP OFC) you can use datasets options label=(var1='label1' var2='label2') in either the proc print data=want() options or even prior to this if you ever plan to do other manipulation of the want dataset you can do it at output time on the proc transpose data=have out=want(rename=(IP=INPATIENT OP=OUTPATIENT OFC=OFFICECLINIC)) For your desired output I'd likely use the label statement in the proc print simply because it allows you to use sentences rather than var names respecting SAS varnames. The big chunk really is how to use proc transpose to get a dataset that respects your output objectives. Proc report procedure wouldn't offer you much more than proc print unless you intended to play a lot with fonts/colors/size etc. *edit corrected for format. Moved it as a statement in the procedure instead of a dataset option. Vincent ... View more