DATA Step, Macro, Functions and more

macros w/ multiple vars and transposing-

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 170
Accepted Solution

macros w/ multiple vars and transposing-

Hello all,

I have a dataset that goes as such:

UNIT     POSITION       MONTH       COMPLIANCE

A          RN                    JAN11     .86

A          MD                    JAN11     .85

B          RN                    FEB11     .90

B          ..                       ..              ..

ZZ       RT                    MAR11       .92

In total there are about 58 units and 20 positions that I need to transpose into a separate report.

SO that it looks like:

POSITION     JAN11 FEB11 MARCH11 TOTAL

RN                .85      .87       .NOOBS              .87

MD               ..        ..         ..              ..

I managed to create a macro that was a total of all the units combined that transposed the data.

However when I attempt to add in the unit and the position into a single macro it does not work.

My code is also below-

In essence, it transposes the data, forms another query to get the formats and the order of the positions and then outputs it in proc format.

Any assistance appreciated. I have tried different approaches w/o success.

Thank you.

Lawrence

WORKING CODE:

%macro groupocc (data=, var=);

   proc sort data=&data(keep=&var) out=values nodupkey ;

    by &var;

   run;

   data _null_;

      set values end=last;

      call symputx('site'||left(_n_),&var);

      if last then call symputx('count',_n_,'g');

   run;

%put _local_;

DATA _NULL_    ;

select(&var);

%do i=1 %to &count;

proc transpose data=ROLEX out=site&i;

id time;

var compliance;

where ROLE="&&site&i";

RUN;

data site&i;

LENGTH ROLE $ 8;

SET site&i;

ROLE="&&site&i";

%END;

%mend groupocc;

%groupocc(data=ROLE, var=ROLE);

%macro combineshpt;

data All;

  set

  %do i = 1 %to &count;

    site&i

%end;;

  run;

%mend;

%combineshpt;

Non-working Code:

%macro groupoccu (data=, var=,var1=);

   proc sort data=&data(keep=&var) out=values nodupkey ;

    by  &var;

   run;

    proc sort data=&data(keep=&var1) out=values1 nodupkey ;

    by  &var1;

   run;

data _null_;

set values end=last;

call symputx('site'||left(_n_),&var);

run;

data _null_;

set values1 end=last;

call symputx('siteb'||left(_n_),&var1);

if last then call symputx('count57',_n_,'g');

run;

%put _local_;

DATA _NULL_    ;

select(&var);

%do i=1 %to &count57;

title4  "Hand Hygiene Compliance for All Occupational Groups For &&site&i ";

DATA SITE&I;

SET ALLUDATA;

IF UNIT="&&SITE&I";

proc transpose data=SITE&I out=siteb&i;

id time;

var compliance;

WHERE ROLE="&&siteb&i";

RUN;

data siteb&i;

LENGTH ROLE $ 8;

SET siteb&i;

ROLE="&&siteb&i";

RUN;

data AllB;

  set

  %do i = 1 %to &count57;

    siteb&i

%end;;

  run;

DATA ALLB;

SET ALLB;

array zero

  • $  &MOS;
  • Do I=1 to DIM(zero);

    if zero=' ' then zero='NO OBS';

    end;

    drop I;

    RUN;

    PROC SQL;

    CREATE TABLE OCC_FINALB AS SELECT DISTINCT A.*, B.ORDER as ORDER1,B.Labelname

    FROM ALLB AS A LEFT JOIN CHILLE.HHROLES AS B ON

    A.ROLE=B.SASNAME

    ORDER BY  ORDER1;

    QUIT;

    proc report data=OCC_FINALB  headline headskip nowd spanrows style(report)={font_size=6pt cellpadding=.3pt cellspacing=.25pt}

    style(column) = {font = ("arial",5pt)};

    ;

    COLUMNS  ORDER1 Labelname &mos ;

    DEFINE ORDER1 /NOPRINT ORDER;

    DEFINE Labelname/ 'Occupational Group'  style={background=lavender};

    RUN;

    */

    %END;

    %mend groupoccu;

    %groupoccu(data=UNITS, var=UNIT, var1=ROLE);


    Accepted Solutions
    Solution
    ‎10-27-2011 08:37 PM
    PROC Star
    Posts: 7,360

    Re: macros w/ multiple vars and transposing-

    LB, Now I've either figured it out or am totally confused.  Based on what you said you had as input, and what you want as a result, doesn't the following one proc transpose do the task?:

    data have;

      informat compliance $12.;

      input bs ROLE $ compliance time $;

      cards;

    1    DietG      100%(3/3)       AUG11

    2    DietG      100%(3/3)       JUL11

    3    DietG      100%(1/1)       SEP11

    4    DietG      100%(7/7)       TOTAL

    5    MDG        94%(100/106)    AUG11

    6    MDG        86%(73/85)      JUL11

    7    MDG        73%(19/26)      SEP11

    8    MDG        88%(192/217)    TOTAL

    9    OTHERS     100%(2/2)       AUG11

    10    OTHERS     100%(2/2)       TOTAL

    11    PCAG       95%(18/19)      AUG11

    12    PCAG       100%(8/8)       JUL11

    13    PCAG       60%(6/10)       SEP11

    14    PCAG       86%(32/37)      TOTAL

    15    PharmG     100%(4/4)       AUG11

    16    PharmG     100%(2/2)       JUL11

    17    PharmG     100%(6/6)       TOTAL

    18    RNG        87%(79/91)      AUG11

    19    RNG        84%(58/69)      JUL11

    20    RNG        91%(42/46)      SEP11

    21    RNG        87%(179/206)    TOTAL

    22    RTG        100%(3/3)       AUG11

    23    RTG        100%(2/2)       JUL11

    24    RTG        75%(3/4)        SEP11

    25    RTG        89%(8/9)        TOTAL

    26    RehabG     80%(4/5)        JUL11

    27    RehabG     100%(1/1)       SEP11

    28    RehabG     83%(5/6)        TOTAL

    29    TOTAL      93%(253/272)    AUG11

    30    TOTAL      86%(172/201)    JUL11

    31    TOTAL      84%(92/109)     SEP11

    32    TOTAL      89%(517/582)    TOTAL

    33    aaG        100%(2/2)       AUG11

    34    aaG        50%(2/4)        JUL11

    35    aaG        67%(4/6)        TOTAL

    36    childG     100%(7/7)       AUG11

    37    childG     100%(2/2)       JUL11

    38    childG     100%(1/1)       SEP11

    39    childG     100%(10/10)     TOTAL

    40    diet2G     100%(1/1)       AUG11

    41    diet2G     100%(1/1)       SEP11

    42    diet2G     100%(2/2)       TOTAL

    43    psaG       100%(17/17)     AUG11

    44    psaG       82%(9/11)       JUL11

    45    psaG       94%(16/17)      SEP11

    46    psaG       93%(42/45)      TOTAL

    47    radioG     100%(3/3)       AUG11

    48    radioG     100%(3/3)       JUL11

    49    radioG     100%(6/6)       TOTAL

    50    socialG    67%(2/3)        JUL11

    51    socialG    67%(2/3)        TOTAL

    52    spiritG    100%(2/2)       JUL11

    ;

    proc transpose data=have out=want (drop=_Smiley Happy;

      by role;

      id time;

      var compliance;

    run;

    View solution in original post


    All Replies
    PROC Star
    Posts: 7,360

    Re: macros w/ multiple vars and transposing-

    Since no one has responded, I have to suggest.  Provide a simple example of your input data, and the output file you want to achieve.  I think that would be a lot easier to understand what you are trying to accomplish.

    Valued Guide
    Posts: 2,174

    macros w/ multiple vars and transposing-


    ~LB

    for your description of the output you need, that looks like a whole lot of work!

    if the "accumulation" of the data is very straightforward, you might be able to make a simple proc means collect all the data

    Otherwise, why not just accumulate the units into an array with one dimension for month and a second for position?

    If you need more than one statistic for each position/month just add another dimension to the array with an entry for each stat needed.

    peterC

    Regular Contributor
    Regular Contributor
    Posts: 170

    Re: macros w/ multiple vars and transposing-

    OK Peter,

    I have gotten the array this far:

    data wide_real;

      set trial;

      array AtopicA(4) $ 12  &mos;

      retain &mos;

      by role time;

      if first.role then do;

      i=1;

        do j = 1 to 4;

         AtopicA = ' ';

        end;

      end;

      AtopicA(i) = compliance;

      if last.role then output;/* outputs only the last obs per person */

      I+1;

    run;

    It works pretty well except when I have missing entries for some months-Then the compliance rates slide left which is incorrect for the month.

    You can tell by the count when it's not 4 (in this case).

    The raw data is below.

    See below-and thank you again.

    LB

    Obs ROLE    compliance   time  count AUG11        JUL11           SEP11    TOTAL        i j

          1 DietG   100%(7/7)    TOTAL   4   100%(3/3)    100%(3/3)    100%(1/1)   100%(7/7)    4 .

          2 MDG     88%(192/217) TOTAL   4   94%(100/106) 86%(73/85)   73%(19/26)  88%(192/217) 4 .

          3 OTHERS  100%(2/2)    TOTAL   2   100%(2/2)    100%(2/2)                             2 .

          4 PCAG    86%(32/37)   TOTAL   4   95%(18/19)   100%(8/8)    60%(6/10)   86%(32/37)   4 .

          5 PharmG  100%(6/6)    TOTAL   3   100%(4/4)    100%(2/2)    100%(6/6)                3 .

          6 RNG     87%(179/206) TOTAL   4   87%(79/91)   84%(58/69)   91%(42/46)  87%(179/206) 4 .

          7 RTG     89%(8/9)     TOTAL   4   100%(3/3)    100%(2/2)    75%(3/4)    89%(8/9)     4 .

          8 RehabG  83%(5/6)     TOTAL   3   80%(4/5)     100%(1/1)    83%(5/6)                 3 .

          9 TOTAL   89%(517/582) TOTAL   4   93%(253/272) 86%(172/201) 84%(92/109) 89%(517/582) 4 .

         10 aaG     67%(4/6)     TOTAL   3   100%(2/2)    50%(2/4)     67%(4/6)                 3 .

         11 childG  100%(10/10)  TOTAL   4   100%(7/7)    100%(2/2)    100%(1/1)   100%(10/10)  4 .

         12 diet2G  100%(2/2)    TOTAL   3   100%(1/1)    100%(1/1)    100%(2/2)                3 .

         13 psaG    93%(42/45)   TOTAL   4   100%(17/17)  82%(9/11)    94%(16/17)  93%(42/45)   4 .

         14 radioG  100%(6/6)    TOTAL   3   100%(3/3)    100%(3/3)    100%(6/6)                3 .

         15 socialG 67%(2/3)     TOTAL   2   67%(2/3)     67%(2/3)                              2 .

         16 spiritG 100%(4/4)    TOTAL   3   100%(2/2)    100%(2/2)    100%(4/4)                3 .

         17 techG   100%(2/2)    TOTAL   2   100%(2/2)    100%(2/2)                             2 .

         18 volG    100%(14/14)  TOTAL   3   100%(12/12)  100%(2/2)    100%(14/14)              3 .

    PROC Star
    Posts: 7,360

    Re: macros w/ multiple vars and transposing-

    It would help if you posted your entire set of code.  You already posted the data in an earlier post, but in this latest post you are using a modified dataset and a macro variable that you didn't mention before.

    Regular Contributor
    Regular Contributor
    Posts: 170

    Re: macros w/ multiple vars and transposing-

    Art;

    As it applies to this specific question-

    What I have thus far is below-

    Thanks for taking a look at it.

    Lawrence

    libname CHILLE "\xx\CHILLE.MDB";

    filename hhmacros "\\xx\MACROS\HH";

    filename mymacros "\\xx\MACROS";

    options mrecall mautosource sasautos=(mymacros hhmacros sasautos);

    RUN;

    /*FOR UNITS*/

    proc sql noprint;

    create table alludata as select distinct left(UNIT) as UNIT,

    CASE WHEN INPUT(SUBSTR(A.Observed_Role_,1,2),8.) in (1:20) THEN C.SASNAME ELSE 'OTHERS' END as ROLE,

    put(fixdate,monyy5.) as time,

    compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance

    from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2)

    where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI')

    GROUP BY UNIT, CALCULATED ROLE,fixdate

    OUTER UNION CORRESPONDING

    select distinct left(UNIT) as UNIT,

    CASE WHEN INPUT(SUBSTR(A.Observed_Role_,1,2),8.) in (1:20) THEN C.SASNAME ELSE 'OTHERS' END as ROLE,

    'TOTAL' AS TIME,

    compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance

    from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2)

    where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI')

    GROUP BY UNIT, CALCULATED ROLE

    OUTER UNION CORRESPONDING

    select distinct left(UNIT) as UNIT,

    'TOTAL' as ROLE,

    put(fixdate,monyy5.) as time,

    compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance

    from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2)

    where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI')

    GROUP BY UNIT, fixdate

    OUTER UNION CORRESPONDING

    select distinct left(UNIT) as UNIT,

    'TOTAL' as ROLE,

    'TOTAL' as time,

    compress(put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END)/count(*),percent10.)||'('||put(SUM(CASE WHEN SUBSTR(Compliance,1,1)='1' THEN 1 ELSE 0 END),8.)||'/'||put(count(*),8.)||')') as compliance

    from SHAREPOINT as A LEFT JOIN CHILLE.HHROLES AS C ON SUBSTR(A.Observed_Role_,1,2)=SUBSTR(C.Sharepoint,1,2)

    where &curyr1<=fixdate<intnx('month',today(),0) and location NOT IN ('LPPI')

    GROUP BY UNIT

    ORDER BY UNIT, CALCULATED ROLE, CALCULATED TIME;

    QUIT;

    proc sql;

    create table trial as select distinct role,compliance,time,count(*) as count

    from alludata

    where unit='06 Long'

    group by role

    order by role,time;

    QUIT;

    data wide_real;

      set trial;

      array AtopicA(4) $ 12  &mos;

      retain &mos;

      by role time;

      if first.role then do;

      i=1;

        do j = 1 to 4;

         AtopicA = ' ';

        end;

      end;

      AtopicA(i) = compliance;

      if last.role then output;/* outputs only the last obs per person */

      I+1;

    run;

    PROC Star
    Posts: 7,360

    Re: macros w/ multiple vars and transposing-

    You missed the one piece that one would need to evaluate your last set of code, namely the value of &mos

    Regular Contributor
    Regular Contributor
    Posts: 170

    Re: macros w/ multiple vars and transposing-

    Sorry Art,

    That value stems from a different program that feeds into this one-

    &MOS concatenates months and total

    In this case the value is

    JUL11 AUG11 SEP11 TOTAL;

    LB

    Solution
    ‎10-27-2011 08:37 PM
    PROC Star
    Posts: 7,360

    Re: macros w/ multiple vars and transposing-

    LB, Now I've either figured it out or am totally confused.  Based on what you said you had as input, and what you want as a result, doesn't the following one proc transpose do the task?:

    data have;

      informat compliance $12.;

      input bs ROLE $ compliance time $;

      cards;

    1    DietG      100%(3/3)       AUG11

    2    DietG      100%(3/3)       JUL11

    3    DietG      100%(1/1)       SEP11

    4    DietG      100%(7/7)       TOTAL

    5    MDG        94%(100/106)    AUG11

    6    MDG        86%(73/85)      JUL11

    7    MDG        73%(19/26)      SEP11

    8    MDG        88%(192/217)    TOTAL

    9    OTHERS     100%(2/2)       AUG11

    10    OTHERS     100%(2/2)       TOTAL

    11    PCAG       95%(18/19)      AUG11

    12    PCAG       100%(8/8)       JUL11

    13    PCAG       60%(6/10)       SEP11

    14    PCAG       86%(32/37)      TOTAL

    15    PharmG     100%(4/4)       AUG11

    16    PharmG     100%(2/2)       JUL11

    17    PharmG     100%(6/6)       TOTAL

    18    RNG        87%(79/91)      AUG11

    19    RNG        84%(58/69)      JUL11

    20    RNG        91%(42/46)      SEP11

    21    RNG        87%(179/206)    TOTAL

    22    RTG        100%(3/3)       AUG11

    23    RTG        100%(2/2)       JUL11

    24    RTG        75%(3/4)        SEP11

    25    RTG        89%(8/9)        TOTAL

    26    RehabG     80%(4/5)        JUL11

    27    RehabG     100%(1/1)       SEP11

    28    RehabG     83%(5/6)        TOTAL

    29    TOTAL      93%(253/272)    AUG11

    30    TOTAL      86%(172/201)    JUL11

    31    TOTAL      84%(92/109)     SEP11

    32    TOTAL      89%(517/582)    TOTAL

    33    aaG        100%(2/2)       AUG11

    34    aaG        50%(2/4)        JUL11

    35    aaG        67%(4/6)        TOTAL

    36    childG     100%(7/7)       AUG11

    37    childG     100%(2/2)       JUL11

    38    childG     100%(1/1)       SEP11

    39    childG     100%(10/10)     TOTAL

    40    diet2G     100%(1/1)       AUG11

    41    diet2G     100%(1/1)       SEP11

    42    diet2G     100%(2/2)       TOTAL

    43    psaG       100%(17/17)     AUG11

    44    psaG       82%(9/11)       JUL11

    45    psaG       94%(16/17)      SEP11

    46    psaG       93%(42/45)      TOTAL

    47    radioG     100%(3/3)       AUG11

    48    radioG     100%(3/3)       JUL11

    49    radioG     100%(6/6)       TOTAL

    50    socialG    67%(2/3)        JUL11

    51    socialG    67%(2/3)        TOTAL

    52    spiritG    100%(2/2)       JUL11

    ;

    proc transpose data=have out=want (drop=_Smiley Happy;

      by role;

      id time;

      var compliance;

    run;

    Regular Contributor
    Regular Contributor
    Posts: 170

    Re: macros w/ multiple vars and transposing-

    OK Art,

    Thank you and I am a knuclehead. I didn't realize that you can set the transpose statement by a "by" statement after all this time (5 yrs)

    This does help immensely.

    Happy Friday.

    Thank you again.


    Super User
    Posts: 9,671

    macros w/ multiple vars and transposing-

    You do not clear your question.

    What about UNIT, ignore them?

    and Total is to mean AN11 +FEB11+..... ?

    data temp;
    input (UNIT     POSITION )  ($)    MONTH  : monyy5.       COMPLIANCE ;
    format month date9.;
    cards;
    A          RN                    JAN11     .86
    A          MD                    JAN11     .85
    B          RN                    FEB11     .90
    B          MD                    FEB11     .92
    ZZ         RT                    MAR11       .92
    ;
    run;
    proc sort data=temp;by position month;run;
    data _null_;
     set temp end=last;
     by position month;
     if _n_ eq 1 then call execute('data want;');
     if first.position then call execute('position="'||position||'";');
     call execute( put(month,monyy5.)||'='||compliance||';' || 'total+'||compliance||';');
     if last.position then call execute('output;call missing(of _all_);');
     if last then call execute('run;');
    run;
    
    
    

    Ksharp

    Regular Contributor
    Regular Contributor
    Posts: 170

    macros w/ multiple vars and transposing-

    OK KSharp,

    I am still playing with the code-

    Question I have now is what is going on when I am missing months for certain positions-

    It seems to shift under th wrong column on occasion.

    Modification to code is as follows:

    data _null_;

    set trial end=last;

    by  role time;

    if _n_ eq 1 then call execute('data want;');

    if first.role then call execute('position="'||ROLE||'";');

    call execute(time||'="'||compliance||'";');

    if last.role then call execute('output;');

    if last then call execute('run;');

    run;

    Thanks.

    The raw data is:

    bs    ROLE       compliance      time

                                    1    DietG      100%(3/3)       AUG11

                                    2    DietG      100%(3/3)       JUL11

                                    3    DietG      100%(1/1)       SEP11

                                    4    DietG      100%(7/7)       TOTAL

                                    5    MDG        94%(100/106)    AUG11

                                    6    MDG        86%(73/85)      JUL11

                                    7    MDG        73%(19/26)      SEP11

                                    8    MDG        88%(192/217)    TOTAL

                                    9    OTHERS     100%(2/2)       AUG11

                                   10    OTHERS     100%(2/2)       TOTAL

                                   11    PCAG       95%(18/19)      AUG11

                                   12    PCAG       100%(8/8)       JUL11

                                   13    PCAG       60%(6/10)       SEP11

                                   14    PCAG       86%(32/37)      TOTAL

                                   15    PharmG     100%(4/4)       AUG11

                                   16    PharmG     100%(2/2)       JUL11

                                   17    PharmG     100%(6/6)       TOTAL

                                   18    RNG        87%(79/91)      AUG11

                                   19    RNG        84%(58/69)      JUL11

                                   20    RNG        91%(42/46)      SEP11

                                   21    RNG        87%(179/206)    TOTAL

                                   22    RTG        100%(3/3)       AUG11

                                   23    RTG        100%(2/2)       JUL11

                                   24    RTG        75%(3/4)        SEP11

                                   25    RTG        89%(8/9)        TOTAL

                                   26    RehabG     80%(4/5)        JUL11

                                   27    RehabG     100%(1/1)       SEP11

                                   28    RehabG     83%(5/6)        TOTAL

                                   29    TOTAL      93%(253/272)    AUG11

                                   30    TOTAL      86%(172/201)    JUL11

                                   31    TOTAL      84%(92/109)     SEP11

                                   32    TOTAL      89%(517/582)    TOTAL

                                   33    aaG        100%(2/2)       AUG11

                                   34    aaG        50%(2/4)        JUL11

                                   35    aaG        67%(4/6)        TOTAL

                                   36    childG     100%(7/7)       AUG11

                                   37    childG     100%(2/2)       JUL11

                                   38    childG     100%(1/1)       SEP11

                                   39    childG     100%(10/10)     TOTAL

                                   40    diet2G     100%(1/1)       AUG11

                                   41    diet2G     100%(1/1)       SEP11

                                   42    diet2G     100%(2/2)       TOTAL

                                   43    psaG       100%(17/17)     AUG11

                                   44    psaG       82%(9/11)       JUL11

                                   45    psaG       94%(16/17)      SEP11

                                   46    psaG       93%(42/45)      TOTAL

                                   47    radioG     100%(3/3)       AUG11

                                   48    radioG     100%(3/3)       JUL11

                                   49    radioG     100%(6/6)       TOTAL

                                   50    socialG    67%(2/3)        JUL11

                                   51    socialG    67%(2/3)        TOTAL

                                   52    spiritG    100%(2/2)       JUL11

    Regular Contributor
    Regular Contributor
    Posts: 170

    macros w/ multiple vars and transposing-

    Final output should look like this:

    position       AUG11        JUL11              SEP11       TOTAL

                 1    DietG       100%(3/3)       100%(3/3)       100%(1/1)      100%(7/7)

                 2    MDG         94%(100/106)    86%(73/85)      73%(19/26)     88%(192/217)

                 3    OTHERS      100%(2/2)       86%(73/85)      73%(19/26)     100%(2/2)

                 4    PCAG        95%(18/19)      100%(8/8)       60%(6/10)      86%(32/37)

                 5    PharmG      100%(4/4)       100%(2/2)       60%(6/10)      100%(6/6)

                 6    RNG         87%(79/91)      84%(58/69)      91%(42/46)     87%(179/206)

                 7    RTG         100%(3/3)       100%(2/2)       75%(3/4)       89%(8/9)

                 8    RehabG      100%(3/3)       80%(4/5)        100%(1/1)      83%(5/6)

                 9    TOTAL       93%(253/272)    86%(172/201)    84%(92/109)    89%(517/582)

                10    aaG         100%(2/2)       50%(2/4)        84%(92/109)    67%(4/6)

                11    childG      100%(7/7)       100%(2/2)       100%(1/1)      100%(10/10)

                12    diet2G      100%(1/1)       100%(2/2)       100%(1/1)      100%(2/2)

                13    psaG        100%(17/17)     82%(9/11)       94%(16/17)     93%(42/45)

                14    radioG      100%(3/3)       100%(3/3)       94%(16/17)     100%(6/6)

                15    socialG     100%(3/3)       67%(2/3)        94%(16/17)     67%(2/3)

                16    spiritG     100%(3/3)       100%(2/2)       100%(2/2)      100%(4/4)

                17    techG       100%(2/2)       100%(2/2)       100%(2/2)      100%(2/2)

                18    volG        100%(12/12)     100%(2/2)       100%(2/2)      100%(14/14)

    Regular Contributor
    Regular Contributor
    Posts: 170

    macros w/ multiple vars and transposing-

    Peter-I forgot about arrays! That might fit the bill and avoid a whole messier bout with macros.

    Ksharp-interesting solution. Have to play with it.

    I have attempted to cut and paste raw data w/o success. I am not always with the new format.

    Thanks again for the suggestions.

    LB

    Regular Contributor
    Regular Contributor
    Posts: 170

    Re: macros w/ multiple vars and transposing-

    OK I got it:

    Raw data:

    Obs   unit   role                                                  time    Compliance

       1  13 ICU  02)  MD/NP/PA (Provider)                            Aug 2011  1) Did Clean Hands

       2  09 ICU  01)  RN                                             Mar 2011  1) Did Clean Hands

       3  09 ICU  visitor                                             Sep 2010  1) Did Clean Hands

       4  10 CVT  01)  RN                                             Nov 2010  2) Did not Clean Hands

       5  15 ICN  Cardiac Echo                                        Jun 2011  1) Did Clean Hands

       6  10 ICC  01)  RN                                             Mar 2011  1) Did Clean Hands

       7  09 ICU  01)  RN                                             Sep 2010  2) Did not Clean Hands

       8  13 ICU  02)  MD/NP/PA (Provider)                            Jan 2011  1) Did Clean Hands

       9  MZ ICU  01)  RN                                             May 2011  1) Did Clean Hands

      10  10 CVT  02)  MD/NP/PA (Provider)                            Mar 2011  1) Did Clean Hands

      11  10 ICC  01)  RN                                             Feb 2011  1) Did Clean Hands

      12  09 ICU  01)  RN                                             Sep 2011  1) Did Clean Hands

      13  13 ICU  01)  RN                                             Sep 2010  1) Did Clean Hands

      14  10 CVT  03)  Respiratory Therapist (RT)                     May 2011  1) Did Clean Hands

      15  13 ICU  12)  Laboratory                                     Aug 2010  1) Did Clean Hands

      16  10 ICC  02)  MD/NP/PA (Provider)                            Feb 2011  1) Did Clean Hands

      17  10 CVT  01)  RN                                             Dec 2010  1) Did Clean Hands

      18  10 CVT  04)  Patient Care Assistant (PCA)                   Nov 2010  2) Did not Clean Hands

      19  MZ ICU  01)  RN                                             Feb 2011  1) Did Clean Hands

      20  09 ICU  01)  RN                                             Jun 2011  1) Did Clean Hands

      21  15 ICN  01)  RN                                             Jan 2011  1) Did Clean Hands

      22  15 ICN  01)  RN                                             Feb 2011  1) Did Clean Hands

      23  15 ICN  01)  RN                                             Sep 2010  1) Did Clean Hands

      24  10 CVT  05)  Hospitality Staff (PSA; Transport;Custodians)  Nov 2010  2) Did not Clean Hands

      25  MZ ICU  01)  RN                                             Sep 2011  1) Did Clean Hands

      26  10 CVT  13)  Radiology                                      Jan 2011  1) Did Clean Hands

      27  10 CVT  02)  MD/NP/PA (Provider)                            Nov 2010  1) Did Clean Hands

      28  10 ICC  01)  RN                                             Aug 2011  2) Did not Clean Hands

      29  15 ICN  01)  RN                                             Oct 2011  1) Did Clean Hands

      30  MZ ICU  01)  RN                                             Jan 2011  1) Did Clean Hands

    After raw data has been processed in PROC SQL (small output)

                              Obs     UNIT      ROLE      time      compliance

                                1    06 Long    DietG     AUG11    100%(3/3)

                                2    06 Long    DietG     JUL11    100%(3/3)

                                3    06 Long    DietG     SEP11    100%(1/1)

                                4    06 Long    MDG       AUG11    94%(100/106)

                                5    06 Long    MDG       JUL11    86%(73/85)

                                6    06 Long    MDG       SEP11    73%(19/26)

                                7    06 Long    OTHERS    AUG11    100%(2/2)

                                8    06 Long    PCAG      AUG11    95%(18/19)

                                9    06 Long    PCAG      JUL11    100%(8/8)

                               10    06 Long    PCAG      SEP11    60%(6/10)

    ☑ This topic is SOLVED.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 14 replies
    • 301 views
    • 1 like
    • 4 in conversation