cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
tipscode
‎07-25-2017
  • 13 Posts
  • 5 Likes Given
  • 0 Solutions
  • 1 Likes Received

Last proc tabulate in large ods csv file doesn't show on initial csv f...

by Obsidian | Level 7 in ODS and Base Reporting
‎06-09-2017 01:51 PM
‎06-09-2017 01:51 PM
I am generating a large number of proc tabulates (30 or so) using ods csv all in SAS 9.4, and the last table doesn't show when I select to open the csv file from the initial prompt. When I try to open this initial file from the file destination - still no last table.   I have to manually select the table from Results, click on the csv icon under the table itself, to open another csv file that will have the last table.   Is there a way to resolve this so that the last table will appear on the initial run?   Thank you. ... View more

Re: Force all values of categorical variable to appear in proc tabulat...

by Obsidian | Level 7 in ODS and Base Reporting
‎05-24-2017 03:38 PM
‎05-24-2017 03:38 PM
Thank you.   In the end, I created a format for the 52 US states and applied them to the proc tabulate like so:             PROC TABULATE NOSEPS MISSING FORMAT=7. DATA=tbls; CLASS STATEFIP /PRELOADFMT; CLASS SERVICES YR; TABLES YR, STATEFIP, SERVICES / PRINTMISS MISSTEXT='0'; WHERE YR = &SAMPLEYR1; FORMAT SERVICES SVC. STATEFIP $STATE.; RUN; ... View more

Force all values of categorical variable to appear in proc tabulate wi...

by Obsidian | Level 7 in ODS and Base Reporting
‎05-23-2017 08:52 AM
‎05-23-2017 08:52 AM
In SAS 9.4 I have a dataset where there are US states with missing data for all variables and would like to have those states to still show up in proc tabulate.   I was considering making an empty dataset with simply all 52 states, along with a dummy variable default to 0, and merge this dataset with the existing dataset to do this.   Thank you in advance for any suggestions. ... View more

Re: create names for multiple htms using SAS macro

by Obsidian | Level 7 in ODS and Base Reporting
‎04-28-2017 01:54 PM
‎04-28-2017 01:54 PM
Thank you Cynthia. ... View more

Re: create names for multiple htms using SAS macro

by Obsidian | Level 7 in ODS and Base Reporting
‎04-27-2017 03:28 PM
‎04-27-2017 03:28 PM
Thank you! Follow-up question: Does single quotes or double quotes matter for 'CA'?   I have been going through past articles from Art Carpenter and think that his macro reference would help for this and other tasks. Are there any other references that I can read up on? ... View more

create names for multiple htms using SAS macro

by Obsidian | Level 7 in ODS and Base Reporting
‎04-27-2017 02:56 PM
‎04-27-2017 02:56 PM
I would like to name the title of a html output using a sas macro that will run with a do loop for multiple iterations in SAS 9.4:   The current code is %macro test (st,xst1);                                .....                                %mend test;                                               followed by, e.g., %test ('CA',CA15);                                             run;   The dataset where the table (in HTML format) will be output has the following where statement:                                  where statefip IN (&st);    There is a year variable where there are observations with '15 values. Is there a way that I can draw this information out as a macro variable to apply for all files?   I am confused about the logic behind "xst1".   Thank you in advance for any comments and suggestions! ... View more

Re: Generating percentages or ratios from two categorical variables in...

by Obsidian | Level 7 in ODS and Base Reporting
‎04-27-2017 07:23 AM
‎04-27-2017 07:23 AM
Thank you so much! This works.   The original datasets have two variables, statefip (the full name of the US state that will be converted into abbreviated two letter states) as well as yr as a 8 digit numeric variable, with values including 2012, 2013, 2014, 2015.    Below is the data that can be derived from these original variable via proc freq:   Can proc sql follow an output dataset generated by proc tabulate to calculate the proportions without using cards?  Is this not possible because state is a character variable?   Probably not ideal, but I can also export this into Excel, calculate the proportions, and then import the Excel as a SAS dataset. ... View more

Re: Generating percentages or ratios from two categorical variables in...

by Obsidian | Level 7 in ODS and Base Reporting
‎04-26-2017 03:09 PM
‎04-26-2017 03:09 PM
My main challenge has been to create counts of each year for the states, then generating new variables that has the proportion of the counts for states. So far I have not found a way to do this in a data step instead of a proc means/freq/tabulate. ... View more

Re: Generating percentages or ratios from two categorical variables in...

by Obsidian | Level 7 in ODS and Base Reporting
‎04-26-2017 09:48 AM
‎04-26-2017 09:48 AM
Thank you both for your timely suggestions!   I would like to do this dynamically for future reports. Here is the code that I have thus far for your comments and suggestions: 1) Creating a macro variable across years:     %LET CYR1 = %EVAL(2015); */For later iterations, one only have to update this year     %LET CYR2 = %EVAL(&CYR1-1);     %LET CYR3 = %EVAL(&CYR1-2);     %LET CYR4 = %EVAL(&CYR1-3);    2) I needed to concatenate and then subset two large datasets that will have new variables by the years:      DATA test;      SET dataset1 dataset2;      state=stnamel(statefip);      IF yr IN (&CYR1 &CYR2 &CYR3 &CYR4);      YR1 = yr IN (&CYR1); */This creates counts per year, but I also see that it creates a binary variable where YR1 can be 0 or 1, I      need to figure out how to take out the values where YR1=0      YR2 = yr IN (&CYR2);      YR3 = yr IN (&CYR3);      YR4 = yr IN (&CYR4);      RUN;   3) Right now I am thinking about using Proc Tabulate to calculate the proportions one column at a time. The code below is in dev because I need to figure out how to take out values where YR1=0, YR2=0, YR3=0, YR4=0. Can I use PCTN in the proc tabulate below? I don't think I can use PCTNSUM.       PROC TABULATE DATA = test;     CLASS state;     VAR YR3 YR4;     TABLE state, YR3 YR4 YR3*PCTN<YR4>='Ratio 2013/2012'*f=8.2;     Where YR3='1' and YR4='1'; */Here is the logic but probably won't be a line of code     RUN;   I would like to successfully execute 3), then set a conditional statement that if any ratio calculated, (e.g., ratio 2013/2012) is lt 70%, then drop the value or convert it to missing "."   Thank you again. ... View more

Re: Generating percentages or ratios from two categorical variables in...

by Obsidian | Level 7 in ODS and Base Reporting
‎04-25-2017 03:23 PM
1 Like
‎04-25-2017 03:23 PM
1 Like
Apologies - I should have clarified with an illustrative table.   I currently am using proc tabulate to generate the following:   STATEFIP 2012 2013 2014 2015 Alabama  9,653  10,264  9,557  11,152 Alaska  6,273  6,476  6,423  6,576 Arizona  24,199  21,635  22,835  20,865   I would like to generate the following table: STATEFIP 2012 2013 2014 2015 2013/2012 2014/2013 2015/2014 Alabama  9,653  10,264  9,557  11,152 106.33% 93.11% 116.69% Alaska  6,273  6,476  6,423  6,576 103.24% 99.18% 102.38% Arizona  24,199  21,635  22,835  20,865 89.40% 105.55% 91.37% ... View more

Generating percentages or ratios from two categorical variables in SAS

by Obsidian | Level 7 in ODS and Base Reporting
‎04-25-2017 02:07 PM
‎04-25-2017 02:07 PM
I need to output percentages in SAS v9.4 that fall above a certain threshold from two categorical variables in a dataset - state and year. I first produced the following table that has all of the data:       proc freq data=example;     tables statefip * yr/ nopercent nocol norow missing out=test;     run;   Now I would like to calculate a percentage from year to year by state. There will be 10 years worth of data. For example, ratio1 = (Count of statefip variable from 2013)/(Count of statefip variable from 2012). If ratio1 is greater than a threshold (e.g., 80%), I would keep the data. If not, it would be coded as "missing". Is that possible to do in SAS?   In addition, is there a way to do this in a macro?   Any suggestions is greatly appreciated! ... View more

Re: How to use a macro to generate htms across years

by Obsidian | Level 7 in ODS and Base Reporting
‎04-21-2017 01:52 PM
‎04-21-2017 01:52 PM
Thank you so much Tom! Two follow-up questions: 1) Currently all states are spelled out in the original dataset (as variable "statefip", with individual value such as "Alabama"), and a label is applied for the abbreviation ( attrib statefip label='STATE: '; ). The original macro uses "where statefip IN (&st)" - Would this work with the changes that you recommended? 2) Can a macro be used to create each htm's name as "State Abbreviation and Year", e.g., "HI15"?     ... View more

How to use a macro to generate htms across years

by Obsidian | Level 7 in ODS and Base Reporting
‎04-21-2017 12:02 PM
‎04-21-2017 12:02 PM
I need to generate 50+ htm tabs (by state) across at least 3 years using a large dataset in SAS 9.4. Currently I am generating the 50+ tabs year by year, but would like to try a nested macro so that multiple consecutive years would be run (e.g., 2013, 2014, 2015). I am also having problems creating a macro variable using the numeric variable "year". Below is the current code. Any comments or suggestions are much appreciated!   %include "&path\tableEditor.tpl"; run; %macro testtab(st,xst1);ods tagsets.tableeditor file="&path\&xst1..htm"options( frozen_headers="yes" frozen_rowheaders="yes" banner_color_even="beige" banner_color_odd="white" header_bgcolor="teal" header_fgcolor="white" rowheader_bgcolor="lightblue" gridline_color="gray" header_size="12" rowheader_size="12" data_size="11"; %macro testtab(st,xst1); ods tagsets.tableeditor file="&path\&xst1..htm" options( frozen_headers="yes" frozen_rowheaders="yes" banner_color_even="beige" banner_color_odd="white" header_bgcolor="teal" header_fgcolor="white" rowheader_bgcolor="lightblue" gridline_color="gray" header_size="12" rowheader_size="12" data_size="11" /*sheet_name="&xst1"*/ ); PROC TABULATE NOSEPS MISSING FORMAT=comma7. FORMCHAR=' ' data=t; CLASS STATEFIP YR sub1; class age sex race ethnic / style={background=lightyellow}; tables statefip, all*(n f=5.1*pctn<sub1 all>) (sex all)*(pctn<sex all>)*f=5.1 (age all)*(pctn<age all>)*f=5.1 (race all)*(pctn<race all>)*f=5.1 (ethnic all)*(pctn<ethnic all>)*f=5.1, all sub1 / rts=16 misstext='--' box=_page_ printmiss; format age ageg. sex sex. race race. ethnic ethnic. sub1 sub. statefip $state.; keylabel all='Total' n='No.' pctn='%'; title1 height=11pt 'Title A,'; title2 height=11pt 'Title B'; title3 height=11pt 'Year = 2013'; */Need to make this a macro*/ where statefip IN (&st); run; ods tagsets.tableeditor close; %mend testtab; %testtab('AL',AL15) %testtab('AR',AR15)...;run;   ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎07-25-2017 10:38 AM
Likes given to
View All
Likes from
View All
Quick links