Select certain rows to transpose for a column?

[Cruise]

Hi Folks,

I'm trying to report my parameter estimates from proc genmod in the way that as a table shown below. I tried proc tabulate and proc report with no success.How to create this table using mock data provided?

Any suggestions and hints are greatly appreciate!

Thanks for your precious time in advance.

DATA HAVE;
INPUT PARAMETER $	SITES $	RR	LCL	UCL;
CARDS;
PM25	LIVER	1.01	1.01	1.02
NYC	LIVER	1.18	1.04	1.33
NO2	LIVER	0.98	0.95	1.00
NYC	LIVER	1.10	0.98	1.23
SO2	LIVER	0.99	0.98	1.00
NYC	LIVER	0.79	0.73	0.85
NO2	STOMACH	0.85	0.79	0.91
NYC	STOMACH	0.81	0.75	0.87
PM25	STOMACH	0.76	0.71	0.82
NYC	STOMACH	0.72	0.66	0.77
NO2	STOMACH	0.67	0.62	0.73
NYC	STOMACH	0.63	0.57	0.68
SO2	STOMACH	0.58	0.53	0.64
NYC	STOMACH	0.54	0.48	0.59
PM25	EYE	0.49	0.44	0.55
NYC	EYE	0.45	0.40	0.50
NO2	EYE	0.40	0.35	0.45
NYC	EYE	0.36	0.31	0.41
SO2	EYE	0.99	0.98	1.00
NYC	EYE	0.79	0.73	0.85
;
PROC PRINT; RUN; 

/*CODES DIDN'T WORK*/
proc tabulate data=HAVE;
var RR LCL UCL;
class SITES PARAMETER;
tables (SITES=' '),(RR LCL UCL)*SUM=' '/nocellmerge;
run;
proc report data=HAVE;
   columns SITES Parameter,(RR LCL UCL);
   define SITES /group;
run;

[Reeza]

You have to modify your structure. Let's go take a look at the first two records:

PM25	LIVER	1.01	1.01	1.02
NYC	LIVER	1.18	1.04	1.33

How do we know which is adjusted and which is NYC? And there's a record with a value in PM25 and NYC but there's no way to figure out what record that is in the table except based on positioning. So you need to restructure your data and I don't know how to do that easily. I suspect there's perhaps something else in your data that may allow for this more easily. 

You need to add another variable so you have the following three columns:

mType = Adjusted RR or NYC

Parameter =  PM25, NO2, SO2

Sites = Liver, Stomach, Eye

Then you can use PROC TABULATE or PROC REPORT. 

[Cruise]

Thanks for reassuring. I'm limited to the structure produced by PROC GENMOD. What you said is what I was wondering about too. I created a dummy variable: if parameter in ('NYC') then NYC=1; else NYC=0. But stuck at this point thinking what you wrote here, if i understood you correctly.

[Cruise]

@novinosrin 

do you know if PROC SQL is helpful in achieving this goal? maybe making use of where statement? The problem is that doing it manually in Excel is not being an option because of huge information in hand.

[novinosrin]

Hi @Cruise 

Do you mean the following report by proc sql?

I,m afraid no, coz this is more of contingency table structure aka cross tab/pivot.

Best is proc tabulate/report over which my knowledge is limited for the reason I didn't have any interest yet to learn. I need something video games lol to get me interested.

But Hang in there Ksharp, Ballardw, MKeintz are excellent in proc tabulate and report. If i were to , it may not be quick

[Cruise]

I agree. Most good coders have a good sense of 3D imagination to code well. Unrelated, one of my colleagues study how to use the principles of Game theory in helping kids attracted to math 🙂

[novinosrin]

@Cruise  Do not hesitate or feel shy if you wanna seek Ballardw or KSharp etc help if it's urgent besides the help you get from kind @Reeza 

I did not call them with an @  for the reason, that would be  unethical for me to request on your behalf. But I know their knowledge on proc tab and report is prudent. 

[Reeza]

Looking at that table, nothing else, can you identify the RR and Adjusted Rate for NO2?

Not using positional information (ie after x row).

If you can't, you can't the computer either. 

---

@Cruise wrote:

Thanks for reassuring. I'm limited to the structure produced by PROC GENMOD. What you said is what I was wondering about too. I created a dummy variable: if parameter in ('NYC') then NYC=1; else NYC=0. But stuck at this point thinking what you wrote here, if i understood you correctly.

---

[Cruise]

@Reeza 

I think I can. Would you agree?

when sites=liver and parameter=NO2 then type='Adjusted RR';
when sites=liver and parameter=NYC then type='RR associated with NYC controlling for other variables';

data came from:

ODS OUTPUT   ParameterEstimates=&NYC;
PROC GENMOD DATA=have;
BY CANCER_TYPE NOTSORTED;
CLASS AGECAT(REF="5") RACETH(REF="1") NYC(REF="1")/PARAM=REF; 
MODEL N_COUNTY= &ejvar AGECAT RACETH PBLACK PASIAN PHISP NYC/DIST=POISSON LINK=LOG OFFSET=LN MAXITER=1000;
RUN;

DATA have1(drop=df ChiSq LowerWaldCL UpperWaldCL ProbChiSq StdErr Level1); 
LENGTH PARAMETER $11.; 
SET &NYC;
RR=round(EXP(ESTIMATE),.001);
LCL=round(EXP(LowerWaldCL),.001);
UCL=round(EXP(UpperWaldCL),.001); 
WHERE Parameter NOT IN ("Scale","Intercept","SEX","AGECAT","RACETH","PBLACK","PASIAN","PHISP");
FORMAT RR F12.3; 
RUN;

[Reeza]

Post that as a data step and I'll take another look at PROC TABULATE, I have some free time this afternoon.

[ballardw · Accepted solution]

Given your example data this comes close.

DATA HAVE;
INPUT PARAMETER $	SITES $	RR	LCL	UCL;
   length col $ 5 row $ 11;
   retain col ;
   altsite=sites;
   if parameter in ('PM25' 'NO2' 'SO2') then do; 
      col=parameter;
      row='Adjusted RR';
   end;
   else row=parameter;
CARDS;
PM25	LIVER	1.01	1.01	1.02
NYC	LIVER	1.18	1.04	1.33
NO2	LIVER	0.98	0.95	1.00
NYC	LIVER	1.10	0.98	1.23
SO2	LIVER	0.99	0.98	1.00
NYC	LIVER	0.79	0.73	0.85
NO2	STOMACH	0.85	0.79	0.91
NYC	STOMACH	0.81	0.75	0.87
PM25	STOMACH	0.76	0.71	0.82
NYC	STOMACH	0.72	0.66	0.77
NO2	STOMACH	0.67	0.62	0.73
NYC	STOMACH	0.63	0.57	0.68
SO2	STOMACH	0.58	0.53	0.64
NYC	STOMACH	0.54	0.48	0.59
PM25	EYE	0.49	0.44	0.55
NYC	EYE	0.45	0.40	0.50
NO2	EYE	0.40	0.35	0.45
NYC	EYE	0.36	0.31	0.41
SO2	EYE	0.99	0.98	1.00
NYC	EYE	0.79	0.73	0.85
;
run;


proc report data=have;
   column sites row altsite  col,(rr lcl ucl);
   define row /group;
   define sites/ group noprint;
   define col / across;
   define altsite/ 'Sites' group;
run;

       

One of the issues with tabulate and report both is that you want to sort by the second column and not the first.

Proc report will allow grouping by a value and not displaying it. By computing the variable altsite in asdata step instead of compute block you get a second value to display where desired.

[Cruise]

Perfect! Amazing! Thank you so much.