topic Re: problem with nested columns in proc report in SAS Procedures

problem with nested columns in proc report

bmozdemir — Mon, 16 Feb 2015 15:45:56 GMT

Hi everybody,

I want to classify the value of BMI and report the value in two columns (BMI<30 and BMI>=30). Under BMI<30 column, values less than 30 should be reported and if the value is higher than 30 a "-" sign should be printed. But I can't get it done with the code below. Could someone kindly provide me some help?

Thanks.

proc report

data = rawdata;
column id_no sequence gender ethnic_group age height weight BMI,(BMI_low BMI_high) BSA;

   define random_no      /order order=internal left "Subject|No." format=z3.;
   define sequence        /order order=internal left "Sequence" style=[cellwidth=1.41in];
   define gender            /center "Gender" style=[cellwidth=.5in];
   define ethnic_group /center width=11 "Ethnic Group" style=[cellwidth=.75in];
   define age                /center width=11 "Age|(years)" style=[cellwidth=.5in];
   define height            /center width=11 "Height|(cm)" style=[cellwidth=.5in] format=comma5.1;
   define weight           /center width=11 "Weight|(kg)" style=[cellwidth=.5in] format=comma5.1;
   define BMI               /across "BMI|(kg/m2)" ;
   define BMI_low       /computed "BMI<30" ;
   define BMI_high     /computed "BMI>=30" ;
   define BSA             /center width=11 "BSA|(m2)" format=comma4.1 style=[cellwidth=.5in] format=comma5.1;

compute BMI_low;
if BMI<30 then BMI_low=BMI;

else BMI_low="-";
endcomp;

compute BMI_high;
if BMI>=30 then BMI_high=BMI;
else BMI_high="-";
endcomp;

run;

Re: problem with nested columns in proc report

Cynthia_sas — Tue, 17 Feb 2015 00:25:42 GMT

Hi:

Understanding the structure of your data will be essential in order for anyone to help with this request. You have specified data=rawdata in your code. Can you post a sample of your data file so that no one has to make up dummy data to aid you with this question. Otherwise, if you feel you cannot post data, then you might want to open a track with Tech Support for more confidential help.

cynthia

Re: problem with nested columns in proc report

Ksharp — Tue, 17 Feb 2015 06:24:13 GMT

According to your describing , BMI_low , BMI_high should be character type characer.

compute BMI_low /character length=10;
if BMI<30 then BMI_low=BMI;

else BMI_low="-";
endcomp;

compute BMI_high/ character length=10;
if BMI>=30 then BMI_high=BMI;
else BMI_high="-";
endcomp;

run;

And the solution I recommend is making a PICTURE format and format BMI with it . As Cynthia pointed out , you need to post a sample data .

Xia Keshan

Re: problem with nested columns in proc report

bmozdemir — Tue, 17 Feb 2015 08:38:12 GMT

Hi Cynthia and Xia. Thank you for your messages. I prepared a dummy data copied it below. I also tried Xia's suggestion but it didn't work. Waiting for your suggestions.

Thank you,

Regards,

Murat

data rawdata;

infile datalines delimiter=',';

input random_no sequence $ gender $ ethnic_group $ age height weight BMI BSA;

datalines;

1,R/T,Male,Caucasian,21,172.6,71.1,23.8,1.8

2,R/T,Male,Caucasian,23,177.8,72.1,22.7,1.9

3,R/T,Male,Caucasian,21,171.9,67.5,22.8,1.8

4,T/R,Male,Caucasian,19,173.9,74.2,24.5,1.9

5,R/T,Male,Caucasian,19,171.3,55.3,18.8,1.6

6,T/R,Male,Caucasian,23,180.1,84.6,26,2

7,T/R,Male,Caucasian,21,180.8,85.9,26.2,2.1

8,T/R,Male,Caucasian,23,177.9,94.9,29.9,2.1

9,R/T,Male,Caucasian,22,174.4,57.2,18.8,1.7

10,T/R,Male,Caucasian,22,174.3,55.5,18.2,1.7

;

Re: problem with nested columns in proc report

Ksharp — Tue, 17 Feb 2015 13:24:06 GMT

I don't understand why you have BMI across usage ?

data rawdata;

infile datalines delimiter=',';

input random_no sequence $ gender $ ethnic_group $ age height weight BMI BSA;

datalines;

1,R/T,Male,Caucasian,21,172.6,71.1,23.8,1.8

2,R/T,Male,Caucasian,23,177.8,72.1,22.7,1.9

3,R/T,Male,Caucasian,21,171.9,67.5,22.8,1.8

4,T/R,Male,Caucasian,19,173.9,74.2,24.5,1.9

5,R/T,Male,Caucasian,19,171.3,55.3,38.8,1.6

6,T/R,Male,Caucasian,23,180.1,84.6,26,2

7,T/R,Male,Caucasian,21,180.8,85.9,36.2,2.1

8,T/R,Male,Caucasian,23,177.9,94.9,29.9,2.1

9,R/T,Male,Caucasian,22,174.4,57.2,38.8,1.7

10,T/R,Male,Caucasian,22,174.3,55.5,38.2,1.7

;

ods html file='x.html' style=sasweb;

proc report nowd

data = rawdata;

column id_no sequence gender ethnic_group age height weight BMI ('BMI|(kg/m2)' BMI_low BMI_high) BSA;

define random_no /order order=internal left "Subject|No." format=z3.;

define sequence /order order=internal left "Sequence" style=[cellwidth=1.41in];

define gender /center "Gender" style=[cellwidth=.5in];

define ethnic_group /center width=11 "Ethnic Group" style=[cellwidth=.75in];

define age /center width=11 "Age|(years)" style=[cellwidth=.5in];

define height /center width=11 "Height|(cm)" style=[cellwidth=.5in] format=comma5.1;

define weight /center width=11 "Weight|(kg)" style=[cellwidth=.5in] format=comma5.1;

define BMI /display noprint "BMI|(kg/m2)" ;

define BMI_low /computed "BMI<30" ;

define BMI_high /computed "BMI>=30" ;

define BSA /center width=11 "BSA|(m2)" format=comma4.1 style=[cellwidth=.5in] format=comma5.1;

compute BMI_low/character length=10;

if BMI<30 then BMI_low=BMI;

else BMI_low="-";

endcomp;

compute BMI_high/character length=10;

if BMI>=30 then BMI_high=BMI;

else BMI_high="-";

endcomp;

run;

ods html close;

Xia Keshan

Message was edited by: xia keshan

Re: problem with nested columns in proc report

Cynthia_sas — Tue, 17 Feb 2015 13:40:00 GMT

Hi:

Thanks for posting your data. I did not see before that you were using BMI as an ACROSS usage variable. That does not make sense. using BMI as an ACROSS item will cause every unique value of BMI to get it's own column. I do not think this is what you want. Also, all your BMIs are under 30, so there is nothing to test your over 30 condition. I think you can do what you want without using ACROSS and just using BMI 2 times on the report, by creating a user-defined format for the low vs the high.

cynthia

Re: problem with nested columns in proc report

bmozdemir — Tue, 17 Feb 2015 15:17:12 GMT

Hi again,

I posted an image showing what I need. Hope this will clarify the issue. I have a huge dataset and in this data there are BMI values below and above 30. What I need is if the value is >=30 then it should be written under the right column and the other column should be "-" or "NA" or similar...

Thanks,

Murat

Re: problem with nested columns in proc report

Cynthia_sas — Tue, 17 Feb 2015 20:14:40 GMT

Hi:

As I indicated, I think this is easy to do using formats, but without using ACROSS. You will want to read about using aliases with PROC REPORT so you can use a column more than 1 time. Please see the example below. You did not say what destination you wanted, so I just used ODS HTML. There is also an alternate program you could use -- still need aliases, but instead of a format, you will use a COMPUTE block. Still no ACROSS needed.

cynthia

Re: problem with nested columns in proc report

bmozdemir — Tue, 17 Feb 2015 20:36:18 GMT

This is a wonderful solution Cynthia. Thank you so much!