Creating Journal Tables SAS

Reply
Regular Contributor
Posts: 150

Creating Journal Tables SAS

I have been working on creating journal tables in SAS using a couple methods, proc tabulate, proc report, and proc print. One thing I always run into is that I often only want to report "yes" values for binary variables in my tables. Is there a way to specify for SAS to only output the yes values. The percentages should still be out of the total. For instance for the smoking category I would only want to report the percent yes. Here is sample proc tabulate code I am using and below that is the proc print code. The proc print gets its values from a macro that is called. Any help would be greatly appreciated. I use ODS rtf to output the tables to word. I am trying to create tables that can easily be inserted into a journal article. Any help would be greatly appreciated.

proc tabulate data=bldata_complete missing order=formatted;

  class dishero overall gender race_new smoke;

  classlev overall  gender race_new smokestyle=[cellwidth=3in asis=on];

  tables gender race_new smoke,

  overall=''*(n*f=4.0 colpctn='(%)'*f=pctfmt.)

  dishero='outcome'*(n*f=4.0 colpctn='(%)'*f=pctfmt.)/misstext='0' rts=15;

  format overall overall.  gender genderfmt. race_new race4fmt. smoke yesno.;

run;

proc print data=last1 label noobs;

Title1 'Title';

Title2 'Table 1 . Demographics';

var varname var1 arma armb totals p;

label  varname="Variable" var1="Response     " arma="Homeless" armb="Not Homeless" totals="Total";

run;

Super User
Super User
Posts: 7,401

Re: Creating Journal Tables SAS

Hi,

Not sure I understand your request fully.  Normally I would imagine a journal table as being a table which records changes to data, e.g. Oracle journal tables hold each change over time to the data within the database.  Is this the kind of thing you are after?  If so then check out proc sql ; insert into syntax.  Create an empty journal table e.g:

proc sql;

     create table JOURNAL

     (

          VARNAME char(200),

          VAR1 char(200),

          ARMA char(200)

...

     );

quit;

Then insert any records which fulfill your criteria:

proc sql;

     insert into JOURNAL

     select     "Variable" as VARNAM,

                    "Response" as VAR1,

                    ...

     from       LAST1

     where      /*your condition*/;

quit;

Super User
Posts: 5,256

Re: Creating Journal Tables SAS

Journals in this context has nothing to to with DB journalling functionality - we're dealing with real life journals, such as patient data.

I'm not sure what the proc print you have connects to your yes/no/total problem?

For the proc tabulate part, I don't think you can elements of data that is part of a total calculation. Perhaps you could take a look at proc report which offers more customization.

Another idea is to do your % calculation, but without formatting, and output the result to data sat (using ODS), and then do the formatting output in  the next step, where you can avoid the no's.

Data never sleeps
Regular Contributor
Posts: 150

Re: Creating Journal Tables SAS

Thanks for your reply. Yes you are correct, I am dealing with participant data and talking about writing journal articles. I often spend hours creating tables and have some of the process set up in SAS to create tables in word but still have to spend considerable time reformatting how I'd like them. I am just learning all of this and have been doing tons of research on creating nicer looking tables. I shouldn't have included the proc print above, they are from two separate sets of code. The proc print calls its data from a macro and creates nice tables but they are large and include tons of information that is not needed. I have been trying to modify that code to fit my needs but am having trouble. Ideally I'd like to create something like in the photo I attached but realize that may not be possible. If I can just get the all, yes, no, and a p-value in that manner that would be great.

Screen Shot 2014-06-19 at 10.15.54 AM.png

Super User
Posts: 17,818

Re: Creating Journal Tables SAS

It can be done.

Here are two references with very different approaches. If you search for clinical reporting on Lexjansen.com you'll see lots of papers. The issue is making something you understand and can maintain and works with your data structure.

http://www.lexjansen.com/pharmasug/2010/tt/tt05.pdf

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

Super User
Posts: 10,497

Re: Creating Journal Tables SAS

I use a lot of dichotomous 1/0 coded variables. But I usually make them VAR and use Sum to get a total of the yes results and then Mean gives the percent (use a format like Percent8. if you don't like percents to look like .734). The use the pctsum functons instead of pctn.

With some example data and what the result should look like maybe could provide more concrete code.

Regular Contributor
Posts: 150

Re: Creating Journal Tables SAS

Thank you for the references to use, I have been using some of those and have been able to create tables just not in exactly the format I want. Most of my variables are dichotomous 1/0 with a few continuous and categorical variables. I put some sample data below, I am not sure this is what you were looking for. The structure is similar to my data but I prefer not to include my actual data as it is a large file.  pid=ID number, gender (1=male, 0=female), age (continuous), hlthpln (1=yes, 0=no), usesyr (1=yes, 0=no), heat (1=yes, 0=no), likeinf (1=white, 2=hispanic, 3=other). The outcome is heat. I would like the output to ideally look like what I posted above with a total column, columns for yes and no for outcome, an OR, 95% CI, and p-value. I would also be happy without the OR and 95% CI but those would be a bonus. I only want to report yes (1) values in the table for binary variables and mean (sd) for continuous variables. Ideally each variable would take up one row on the table except for categorical variables of course which would include all categories. I have to admit that this level of coding is above my knowledge and I have been able to create basic tables using ODS output but am trying to learn how to create more complicated tables and output. Let me know if you need more information and I will try to clarify. Thank you again, this forum has increased my SAS knowledge immensely.

DATA sample;

   INPUT pid gender age hlthpln usesyr heat racecate;

   DATALINES;

a01  0  35  1  1  1 1

a02  1  50  1  1  1 3

a03  0  45  0  1  0 1

a04  1  24  1  0  0 2

a05  1  52  0  0  1 1

a06  0  44  1  1  0 3

a07  1  34  1  0  1 3

a08  1  40  0  1  0 2

a09  0  47  0  0  0 1

a10  0  35  1  0   1   2

;

Super User
Posts: 10,497

Re: Creating Journal Tables SAS

Since gender is the only variablesin that example that match the variables referenced in the attempted tabulate code it's kind of hard to guess what you want to do.

The example should be from data in your bldata_complete dataset.

Regular Contributor
Posts: 150

Re: Creating Journal Tables SAS

Sorry about that and that you for being willing to help. The datalines below include my outcome which is dishero and the other variables in the proc tabulate. The Overall variable was created only as a header in the proc tabulate table. From what I have found in my research is that is may not be possible to add a p-value in table created by proc tabulate but I could be wrong. As I mentioned before I have a macro that was created by a statistician in my office that also creates tables and then I use the proc print with ods code I past above to output the table to word but I don't know how to modify that to only display yes values in the table and thus the tables turn out to be vary large. Thank you again and let me know if you need more information.

DATA sample;

   INPUT pid gender age hlthpln usesyr dishero race_new smoke;

   DATALINES;

a01  0  35  1  1  1 1  1

a02  1  50  1  1  0 3  1

a03  0  45  0  1  1 1  1

a04  1  24  1  0  0 2  0

a05  1  52  0  0  0 1  0

a06  0  44  1  1  1 3  1

a07  1  34  1  0  1 3  0

a08  1  40  0  1  0 2  1

a09  0  47  0  0  0 1  1

a10  0  35  1  0   1   2  0

;

Ask a Question
Discussion stats
  • 8 replies
  • 728 views
  • 0 likes
  • 5 in conversation