BookmarkSubscribeRSS Feed
rfarmenta
Obsidian | Level 7

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;

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

LinusH
Tourmaline | Level 20

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
rfarmenta
Obsidian | Level 7

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

Reeza
Super User

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

ballardw
Super User

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.

rfarmenta
Obsidian | Level 7

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

;

ballardw
Super User

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.

rfarmenta
Obsidian | Level 7

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

;

Patrick
Opal | Level 21

@rfarmenta

I'm still not 100% sure that I understand what you need. What would really help: Add your Have sample data and the desired want result using the sample data (a table or another data step creating the desired data) into a single post and then explain the logic how you get from the have to the want.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2561 views
  • 0 likes
  • 7 in conversation