topic Re: macros w/ multiple vars and transposing- in SAS Programming

macros w/ multiple vars and transposing-

_LB — Wed, 26 Oct 2011 20:53:09 GMT

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);

Re: macros w/ multiple vars and transposing-

art297 — Wed, 26 Oct 2011 23:25:09 GMT

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.

macros w/ multiple vars and transposing-

Peter_C — Thu, 27 Oct 2011 09:46:26 GMT

~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

macros w/ multiple vars and transposing-

Ksharp — Thu, 27 Oct 2011 11:11:22 GMT

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

macros w/ multiple vars and transposing-

_LB — Thu, 27 Oct 2011 16:24:55 GMT

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.

Re: macros w/ multiple vars and transposing-

_LB — Thu, 27 Oct 2011 16:34:29 GMT

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)

macros w/ multiple vars and transposing-

_LB — Thu, 27 Oct 2011 18:06:28 GMT

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

macros w/ multiple vars and transposing-

_LB — Thu, 27 Oct 2011 18:36:04 GMT

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)

Re: macros w/ multiple vars and transposing-

_LB — Thu, 27 Oct 2011 21:55:49 GMT

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;

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.

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 .

Re: macros w/ multiple vars and transposing-

art297 — Thu, 27 Oct 2011 22:16:47 GMT

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.

Re: macros w/ multiple vars and transposing-

_LB — Thu, 27 Oct 2011 22:23:34 GMT

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,

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,

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,

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;

AtopicA(i) = compliance;

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

I+1;

run;

Re: macros w/ multiple vars and transposing-

art297 — Thu, 27 Oct 2011 22:33:35 GMT

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

Re: macros w/ multiple vars and transposing-

_LB — Thu, 27 Oct 2011 22:39:09 GMT

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;

Re: macros w/ multiple vars and transposing-

art297 — Fri, 28 Oct 2011 00:37:51 GMT

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=_:);

by role;

id time;

var compliance;

run;

Re: macros w/ multiple vars and transposing-

_LB — Fri, 28 Oct 2011 13:52:01 GMT

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.