@michokwu wrote: I am using proc sql to create a table which aggregates variables into w_geocodes by Id2 I think the format is important if it has to be done properly.   If you can propose a different method, that will be great.   Thank you. Perhaps the issue is that the query you posted does not match what you actually want to do?  Can you post a simple set of example input data and what output summary you want to get for that sample data? ... View more

@michokwu wrote: Hello, I'm creating a summary table and want to calculate the weighted counts and sums by group and subgroup. I have this summary by subgroups in different files but I want it as 1 summary table, it goes through 2001 to 2017. When I tried to that, I did not get the desired result.  (See excel sheet attached) and my code below proc tabulate data=analysis1.in out=analysis1.in_w(drop=_type_); var weight; class Year race_sex education; table Year*race_sex*education weight*(n sum); run;   Regards, You really need to provide an example of the data and what you expect the results for that example data to actually be. We need an actual data set to test code against. Your existing code does not show any attempt to calculate a "weighted count" and I am not sure that  you are using the term appropriately.   If you are not getting the desired result you need to show what the actual desired (or expected) result should be. Other wise have to guess, and with out actual data we can't even guess reasonably.   The way you present your "what I want" does not make a lot of sense with the "count" and "sum" in multiple places. I am moderately sure that Proc tabulate will not line up the columns as shown.   Since your have separate tows in our desired output for race_sex and education you do not want to use race_sex*education as the * used that way nests education within race. Do you want the "person weight total You can get a bit closer with : proc tabulate data=analysis1.in out=analysis1.in_w(drop=_type_); var weight; class Year race_sex education; table year weight*(n sum); table Year*race_sex weight*(n sum); table Year*education weight*(n sum); run; But that will not make the columns line up in Excel the way you show. That would be possible with the Report Writing Interface in a data step with appropriate data structure but not with Tabulate.   ... View more

@michokwu wrote: data WORK.ED_TABLE; infile datalines dsd truncover; input Year:$8. Total:$3. Bachelors:$3. Doctorate:$3. HSG:$3. Non_HSG:$3. Masters:$3. Professional:$3. Some_College:$3.; label Year="NAME OF FORMER VARIABLE"; datalines; 2010 100 100 100 100 100 100 100 100 2011 100 104 96 98 94 107 96 105 2012 101 108 106 94 92 109 97 109 2013 103 114 129 94 90 113 99 110 2014 103 117 133 92 89 119 101 111 2015 104 124 136 90 85 127 103 112 2016 104 126 157 88 83 132 111 112 2017 105 129 166 90 77 139 122 112 ;;;; NOTE: There were 8 observations read from the data set WORK.ED_TABLE. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds Series plots are going to expect NUMERIC values for variables used in X= and Y=. Your data step creates only CHARACTER values. Your Year variable only has 4 digits, reading with 8 means that TOTAL is not what you think it might be. DSD is also not needed. data WORK.ED_TABLE; infile datalines truncover; input Year: 4. Total: 3. Bachelors: 3. Doctorate: 3. HSG: 3. Non_HSG: 3. Masters: 3. Professional: 3. Some_College: 3.; label Year="NAME OF FORMER VARIABLE"; datalines; 2010 100 100 100 100 100 100 100 100 2011 100 104 96 98 94 107 96 105 2012 101 108 106 94 92 109 97 109 2013 103 114 129 94 90 113 99 110 2014 103 117 133 92 89 119 101 111 2015 104 124 136 90 85 127 103 112 2016 104 126 157 88 83 132 111 112 2017 105 129 166 90 77 139 122 112 ;;;; proc sgplot data=work.ed_table; series x = Year y = Total / markers markerattrs=(symbol=plus) lineattrs=(pattern=solid); series x = Year y = Non_HSG / markers markerattrs=(symbol=diamondfilled) lineattrs=(pattern=solid); series x = Year y = HSG / markers markerattrs=(symbol=diamond) lineattrs=(pattern=solid); series x = Year y = Some_College / markers markerattrs=(symbol=starfilled) lineattrs=(pattern=solid); series x = Year y = Bachelors / markers markerattrs=(symbol=sqaurefilled) lineattrs=(pattern=solid); series x = Year y = Masters / markers markerattrs=(symbol=trianglefilled) lineattrs=(pattern=solid); series x = Year y = Professional / markers markerattrs=(symbol=homedownfilled) lineattrs=(pattern=solid); series x = Year y = Doctorate / markers markerattrs=(symbol=circlefilled) lineattrs=(pattern=solid); xaxis values= (2010 to 2017 by 1) label='5-Year Annual Estimate'; yaxis Label = 'Educational attainment adjusted for 2010 Baseline Year' Grid Values = (60 to 180 by 20); title1 "Educational attainment for 25years and above"; title2 "Annual Data - Baseline 2010"; run; Yields:   So, there must be something unexpected about your original data set. Note, my colors will likely vary from yours due to my use of the MEADOW style.   And for further consideration: proc transpose data=work.ed_table out=work.ed_trans name= groupvar prefix=value; by year; var Total Bachelors Doctorate HSG Non_HSG Masters Professional Some_College; run; proc sgplot data=work.ed_trans; series x=year y=value1/group=groupvar markers; xaxis values= (2010 to 2017 by 1) label='5-Year Annual Estimate'; yaxis Label = 'Educational attainment adjusted for 2010 Baseline Year' Grid Values = (60 to 180 by 20); title1 "Educational attainment for 25years and above"; title2 "Annual Data - Baseline 2010"; label groupvar='Education Attaintment'; run; ... View more

@michokwu wrote: Thanks. I received error message: ERROR: You are trying to use the character format $PROGRAM with the numeric variable Pr_Code in data set WORK.PATH.   Also, the solution does not address how to find and match the value. I tried to assign the value of pr_code to program_title but could not.  I saw a similar but not exact discussion https://communities.sas.com/t5/SAS-Programming/How-to-assign-a-column-value-based-on-another-column/td-p/217728 but still does not answer my question Since you did not provide data in the form of a data step I have to guess whether values like pr_code are numeric or character. Most things named "code", like Zipcode, are identifiers and generally are not used for numeric calculation and hence are character. Remove the quotes around the pr_code values in the format, remove the $ from both the Proc format definition and the proc print. ... View more