How can I make proc tabulate more accurate when summing numbers? I am getting unacceptably-large rounding errors in SAS compared to doing a pivot table on the same data in excel.   I am using the following command to read the file:   data new_dataset;       infile "&input_loc.\&input_file." dsd missover dlm = "," truncover lrecl=32767 firstobs = 2;      input      .....      sqft :12.2      ....      ;  run;    In the raw excel file with 25,799 rows, every number has two decimals. I have tried reading it in several ways--with no informat, by saving it with quoted commas "2,500.00" and reading it with comma12.2 but it makes no difference. The numbers look the same as what's in excel as far as I can tell, but I am getting a difference of 5,179,191 in the total for proc tabulate, vs 5,179,186 in excel. I have tried adding formats to sqft as well as to the Tabulate statement.    How can I make it tabulate with higher precision? Or, is it possible Excel is wrong?  ... View more

What are some strategies for getting tables of percentages *and* means for survey items?    Say for example I am measuring self-reported job skills. Respondents can answer that their skills for three different items are Weak (0), Fair (1), Good (2), Very good (3), Excellent (4), or that they have never done that task, or they can skip the question. I want to show both the percentages of each of those categories *and* the means      I have been using proc tabulate to calculate the percentages for the scale items. However, if respondents skip an item then they are excluded from the entire procedure, even though their answers should be included for non-skipped items. And then there's the question of means--If I code "I have not done this" as 9 then it will mess up the means.    I have altered a macro that I found here to create the percentage tables by appending each freq output. Is there some way to include the "I have not done this" in that macro that won't mess up the means?    Here's the Macro:      %macro summary_table(dsetin, varlist, dsetout); *delete the previous version of the output dataset; proc datasets nodetails nolist; delete &dsetout; quit; *loop through variable list; %let i=1; %do %while (%scan(&varlist, &i, " ") ^=%str()); %let var=%scan(&varlist, &i, " "); %put &i &var; * This is a "Macro Variable" which can store a value without a dataset; *Cross tab; proc freq data=&dsetin; table &var/ out=temp1 noprint; run; *Get variable label as name; data _null_; set &dsetin (obs=1); call symput('var_name', vlabel(&var.)); run; %put &var_name; *Add in Variable name and store the levels as a text field; data temp2; keep variable value count percent; length variable $200.; Variable = "&var_name"; set temp1; value=input(&var, &format.); percent=percent/100; * store these as decimals instead of numbers; format percent percent8.1; drop &var.; run; %put &var_name; *Append datasets; proc append data=temp2 base=&dsetout force; run; *drop temp tables so theres no accidents; proc datasets nodetails nolist; delete temp1 temp2; quit; *Increment counter; %let i=%eval(&i+1); %end; %mend;   ... View more

Hello, I am trying to merge 4 tables and I would like all the final result to contain all of the values from each table, but when I try to do a full outer join it produces way too many results. The data are structured as shown below: table a has a unique id, a zip code, and a name. Table b has a zip code and a city, table c has a zip code and zcta, and table has a zcta and some data.      data a; input id zip name $; cards; 1 15217 Bob 2 15217 Sue 3 15213 Jack 4 85001 Ginger 5 91111 Mary ; run; data b; input zip city $; cards; 15217 Pittsburgh 15213 Pittsburgh 60290 Chicago 10001 New York 85001 Phoenix ; run; data c; input zip zcta; cards; 15217 15217 15213 15217 51331 51331 60290 60290 10001 10002 85001 85001 ; run; data d; input zcta data $; cards; 15217 v1 51331 v2 60290 v3 10002 v4 85001 v5 95201 v6 ; run; proc sql; create table outer as select a.id, a.name, a.zip, b.city, c.zcta, d.data from a as a full outer join (b as b, c as c, d as d) on a.zip = b.zip and a.zip = c.zip and b.zip = c.zip and c.zcta = d.zcta; quit;   The result I'd like would be:    input id name $ zip zcta city $ data $ ; cards; 1 Bob 15217 15217 Pittsburgh v1 2 Sue 15217 15217 Pittsburgh v1 3 Jack 15213 15217 Pittsburgh v1 4 Ginger 85001 85001 Phoenix v5 . . 10001 10002 NewYork v4 . . 60290 60290 Chicago v3 . . 51331 51331 . v2 . . . 95201 . v6 ; run; Is there a straightforward way to do this? Thank you for your help!  ... View more

I frequently analyze survey research where I would like to calculate row percents by category for several variables. The problem arises when there are different missing values for each variable. For example, imagine 10 people answered a survey with 5 questions (Q1-Q5). Person 1 answered all 5 questions, Person 2 skipped Q3, Person 3 skipped Q2, Person 4 skipped Q1, and so on. So when I calculate the percentages for each question, I want it to be the percentage of people who answered each question, not the total number who participated in the survey. Because the missing values vary I can't set them as class variables. I can set them as var variables easily enough, and I can get the count of the 1s and the count of the non-missing values, but I can't for the life of me figure out how to get the percentage by category. To give an example of what my data look like: data test; input category $ Q1 Q2 Q3 Q4 Q5; datalines; a 1 0 1 0 1 b 1 1 . 1 0 a 0 . 1 1 0 b . 0 1 0 1 a 1 0 1 . 0 b 0 1 0 1 0 a . 1 0 1 0 b 1 0 0 0 0 a 1 0 0 1 1 b 0 1 1 . 0 a 1 0 . 1 1 proc print; run; I know I'm doing something wrong, but this table has the correct counts: proc tabulate data = test format = comma6.; class category; var Q1-Q5; tables q1-q5, (all category)* (all n); run; the "Sum" has the count of 1s, and the N has the count of the 1s and 0s. (i'm not exactly sure where the sum came from, which is part of why I'm sure I'm doing something wrong). The table below is laid out exactly how I would want, except I would like to add another column for each category that has the percent by category for each of the Q1-Q5. proc tabulate data = test format = comma6.; class category; var Q1-Q5; tables Q1-Q5, (all = "All" category = "") * (all = "" n = "Total") / row = float; keylabel sum = "1s"; run; So for example, Q1 would be 5/8 = 63% overall, 75% for category a, and 50% for category b. Maybe I am going about this totally the wrong way, so if anyone has any suggestions I'd really appreciate it! This comes up very frequently for me so I'd really like to find a solution. Thanks! ... View more