topic Re: adding variables in proc summary in Statistical Procedures

adding variables in proc summary

geneshackman — Wed, 19 Oct 2022 23:00:06 GMT

I'd like to know if it's possible to add in variables during proc summary. At present, I have two steps.

First is getting a summary for ROS (Rest Of State), that is, adding up all the values for all the ROS counties (cos means county), that is, adding up number of thingies in each county, so I can get total number of thingies for all the ROS counties together.

Second, in a data step, for the row of total of all these, I'm assigning cos to be 888.

Can I get this summary row cos to = 888 in proc summary?

/* calculating ROS */
proc summary data=black nway;
class region;
var total underpov;
output out=BlackRos0 (drop=_:) sum=;
where cos in (1:57);
run;

/* assigning "888" to the cos */

data blackros; set blackros0;
cos = 888;
run;

Re: adding variables in proc summary

PaigeMiller — Wed, 19 Oct 2022 23:22:55 GMT

On the one hand, you say you want the summary row to have cos=888, but then your code assigns 888 to all rows. Could you please clarify?

PROC SUMMARY does not allow assignment of specific values to specific fields. Could you explain more why you want 888 in the output (either in one row or all rows)??? WHere does the 888 come from, anyway? Is it somehow in the data set named black?

Re: adding variables in proc summary

geneshackman — Wed, 19 Oct 2022 23:42:58 GMT

HI Paige

The proc summary creates a summary data set called BlackRos0. That working file has one row, which is the summary of adding together all the counties. I want to assign "888" to that row.

However, "PROC SUMMARY does not allow assignment of specific values to specific fields." Okay, that's what I wanted to know. I wasn't sure whether it did.

Thanks

Re: adding variables in proc summary

ballardw — Thu, 20 Oct 2022 00:15:35 GMT

Define the values that are "ROS" and for which variable.

I suspect that you can use a format to get that, possibly applied to County, which you would add to the Class statement and remove from the Where so you can see the formatted values.

Maybe this example with a data set you should have available will demonstrate:

proc format ;
value ages
11,12,13 ='11-13'
14-high = '>14';
run;

proc summary data=sashelp.class nway;
   class sex age;
   format age ages.;
   var height;
   output out=example max=;
run;

The format groups all the ages 14 and greater into one report category leaving other single ages in the output. This would be similar to what I think you are doing with "cos". The 14 and older are the "rest of the ages" and the 11 -13 like your Where values.

I suspect if the above example comes close that if you

1)remove Nway

2)leave the _type_ variable in the data you will see a row that has the desired summary. I can't tell which you might want.

Your data step code would then assign that value to COS based on the _type_ value. You might also only keep some of the _type_ values.

Or maybe a Multilabel format:

proc format ;
value otherages (multilabel)
low-high ='Everything'
11 - 13 ='11-13'
14 - high='>14';
run;

proc summary data=sashelp.class nway;
   class sex age/mlf;
   format age otherages.;
   var height;
   output out=example2 max=;
run;

Proc Means/summary is one of the few procedures that can use the multilabel formats to differentiate overlapping groups.

Really, actual data and the desired result would make this go much faster instead of making us guess your intent.

Re: adding variables in proc summary

Reeza — Thu, 20 Oct 2022 00:31:53 GMT

My question is why?

To have the sort order correct when you add it into a table is my guess but there are other ways to get that table/output generated if you show what you're trying to do. But the way you have above definitely works.

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 12:40:13 GMT

Hi all. Thanks for the many suggestions and questions. The original data was from the ACS, b17020 a-i, poverty status by age, one table for each race. This is public domain data. I'm attaching here a modified data set for b17020b, Black or African American Alone. The columns in this data set are:

cos = the code we use for county

total = total number of children under 18 (sum of ages under 6 + age 6-11 + ages 12-17

underpov = total number of children under 18 below poverty level

region = I assigning counties to either NYC or ROS (Rest Of State - New York State excluding New York City)

I had downloaded one data file with data by county, and another file for NY State, and another file for NY City, so this data set has all three. But the Census has no category for ROS, so I wanted to make a row for total and underpov for ROS.

Our codes for NYS = 999, code for NYC = 102, and code for ROS = 888.

I hope this helps.

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 12:42:01 GMT

Please see my response to Reeza, with more details and an attached data set.

Re: adding variables in proc summary

PaigeMiller — Thu, 20 Oct 2022 12:47:15 GMT

@geneshackman wrote:

The proc summary creates a summary data set called BlackRos0. That working file has one row, which is the summary of adding together all the counties. I want to assign "888" to that row.

Unless there is only one value for the CLASS variable named REGION, then BlackRos0 has more than one row. But this does not explain what you are doing or why you need 888 and where does the 888 come from (all things I asked you to provide), so that perhaps we can figure out another way to get what you want.

Most people here will not download and open Microsoft Office files, as they can be security threats. Please provide (a portion of) your data as working SAS data step code, which you can type in yourself or follow these instructions.

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 12:56:25 GMT

Here are a few lines from the data set. See the explanation of these columns in my response to Reeza. The bottom of this sample has one row for New York State (cos = 999) and one row for New York City (cos = 102). I want to add one row for ROS (cos = 888)

cos	total	underpov	region
1	8913	2910	ROS
2	15	2	ROS
58	120347	43223	NYC
3	2923	1423	ROS
4	317	106	ROS
5	338	148	ROS
22	1434	459	ROS
59	167871	43213	NYC
23	66	8	ROS
24	206	114	ROS
999	647954	182762	NYS
102	420784	117128

Re: adding variables in proc summary

PaigeMiller — Thu, 20 Oct 2022 13:30:12 GMT

Why is COS even needed if you have Region? Doesn't one imply the other?

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 13:49:39 GMT

I am going to use cos as the primary code for county. cos is unique to each county. As you can see, all counties in NYC have "NYC" for region, and all counties not in NYC have "ROS" for region. So region is not unique to each county. And ROS and NYC does not uniquely identify the rows which are summaries for the state and for each region. The cos codes do.

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 14:13:33 GMT

Here is the data set in csv if that's more acceptable.

Re: adding variables in proc summary

ballardw — Thu, 20 Oct 2022 14:24:33 GMT

@geneshackman wrote:

Here is the data set in csv if that's more acceptable.

Not really. WE have to make decisions as to how to read the file and our choices may not match yours.

So you should include the code used to read that file into a data set.

OR:

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Re: adding variables in proc summary

ballardw — Thu, 20 Oct 2022 14:23:41 GMT

@geneshackman wrote:

Hi all. Thanks for the many suggestions and questions. The original data was from the ACS, b17020 a-i, poverty status by age, one table for each race. This is public domain data. I'm attaching here a modified data set for b17020b, Black or African American Alone. The columns in this data set are:

cos = the code we use for county

total = total number of children under 18 (sum of ages under 6 + age 6-11 + ages 12-17

underpov = total number of children under 18 below poverty level

region = I assigning counties to either NYC or ROS (Rest Of State - New York State excluding New York City)

I had downloaded one data file with data by county, and another file for NY State, and another file for NY City, so this data set has all three. But the Census has no category for ROS, so I wanted to make a row for total and underpov for ROS.

Our codes for NYS = 999, code for NYC = 102, and code for ROS = 888.

I hope this helps.

Note that Excel, especially if the file actually started as CSV is worse. If you open a CSV in Excel and then save it to XLSX it will change values. The choices of how to read it still apply.

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 14:40:16 GMT

"If you open a CSV in Excel and then save it to XLSX it will change values."

Really? I didn't know that. What values will change? I'm sure you don't mean it will change a 1 to a 2. Perhaps this is about blanks, missings, like that?

Re: adding variables in proc summary

Reeza — Thu, 20 Oct 2022 16:30:17 GMT

Leading zero's can get dropped, dates can be read incorrectly (mmddyy instead of ddmmyy), numbers longer than 16 digits will have the end digits changed if not imported as character.

@geneshackman wrote:

"If you open a CSV in Excel and then save it to XLSX it will change values."

Really? I didn't know that. What values will change? I'm sure you don't mean it will change a 1 to a 2. Perhaps this is about blanks, missings, like that?

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 16:39:14 GMT

"Leading zero's can get dropped, dates can be read incorrectly (mmddyy instead of ddmmyy), numbers longer than 16 digits will have the end digits changed if not imported as character."

Thanks, good to know!

Re: adding variables in proc summary

Tom — Thu, 20 Oct 2022 18:49:53 GMT

Here is how to share data:

data have;
  infile cards dsd truncover ;
  input cos total underpov region $;
cards;
1,8913,2910,ROS
2,15,2,ROS
58,120347,43223,NYC
3,2923,1423,ROS
4,317,106,ROS
5,338,148,ROS
6,681,428,ROS
7,950,185,ROS
8,58,37,ROS
9,212,56,ROS
10,269,17,ROS
11,220,7,ROS
12,59,15,ROS
13,6108,1195,ROS
14,29917,11954,ROS
15,21,13,ROS
16,71,60,ROS
17,147,147,ROS
18,129,53,ROS
19,128,98,ROS
20,0,0,ROS
21,276,47,ROS
22,1434,459,ROS
59,167871,43213,NYC
23,66,8,ROS
24,206,114,ROS
25,65,31,ROS
26,31252,14751,ROS
27,386,171,ROS
28,32295,2973,ROS
60,41041,15816,NYC
29,3887,1680,ROS
30,3584,2158,ROS
31,15001,8066,ROS
32,490,36,ROS
33,9528,1109,ROS
34,207,63,ROS
35,270,96,ROS
36,200,50,ROS
37,561,40,ROS
61,79161,10967,NYC
38,2347,958,ROS
62,12364,3909,NYC
39,9205,1688,ROS
40,47,15,ROS
41,747,193,ROS
42,4122,1594,ROS
43,50,0,ROS
44,13,5,ROS
45,137,28,ROS
46,353,190,ROS
47,25916,3601,ROS
48,1279,631,ROS
49,53,21,ROS
50,615,288,ROS
51,1744,665,ROS
52,84,15,ROS
53,27,1,ROS
54,432,167,ROS
55,28775,4849,ROS
56,36,15,ROS
57,4,4,ROS
999,647954,182762,NYS
102,420784,117128,
;

Why do you have the 999 and 102 data in there? Aren't those something you would DERIVE from the actual 62 counties?

proc summary data=have ;
  where cos < 100 ;
  class cos region ;
  types cos region () ;
  var total underpov ;
  output out=want sum= ;
run;

Result

Obs    cos    region    _TYPE_    _FREQ_     total    underpov

  1      .                 0        62      647954     182762
  2      .     NYC         1         5      420784     117128
  3      .     ROS         1        57      227170      65634
  4      1                 2         1        8913       2910
  5      2                 2         1          15          2
  6      3                 2         1        2923       1423
  7      4                 2         1         317        106
  8      5                 2         1         338        148
  9      6                 2         1         681        428
 10      7                 2         1         950        185
 11      8                 2         1          58         37
 12      9                 2         1         212         56
 13     10                 2         1         269         17
 14     11                 2         1         220          7
 15     12                 2         1          59         15
 16     13                 2         1        6108       1195
 17     14                 2         1       29917      11954
 18     15                 2         1          21         13
 19     16                 2         1          71         60
 20     17                 2         1         147        147
 21     18                 2         1         129         53
 22     19                 2         1         128         98
 23     20                 2         1           0          0
 24     21                 2         1         276         47
 25     22                 2         1        1434        459
 26     23                 2         1          66          8
 27     24                 2         1         206        114
 28     25                 2         1          65         31
 29     26                 2         1       31252      14751
 30     27                 2         1         386        171
 31     28                 2         1       32295       2973
 32     29                 2         1        3887       1680
 33     30                 2         1        3584       2158
 34     31                 2         1       15001       8066
 35     32                 2         1         490         36
 36     33                 2         1        9528       1109
 37     34                 2         1         207         63
 38     35                 2         1         270         96
 39     36                 2         1         200         50
 40     37                 2         1         561         40
 41     38                 2         1        2347        958
 42     39                 2         1        9205       1688
 43     40                 2         1          47         15
 44     41                 2         1         747        193
 45     42                 2         1        4122       1594
 46     43                 2         1          50          0
 47     44                 2         1          13          5
 48     45                 2         1         137         28
 49     46                 2         1         353        190
 50     47                 2         1       25916       3601
 51     48                 2         1        1279        631
 52     49                 2         1          53         21
 53     50                 2         1         615        288
 54     51                 2         1        1744        665
 55     52                 2         1          84         15
 56     53                 2         1          27          1
 57     54                 2         1         432        167
 58     55                 2         1       28775       4849
 59     56                 2         1          36         15
 60     57                 2         1           4          4
 61     58                 2         1      120347      43223
 62     59                 2         1      167871      43213
 63     60                 2         1       41041      15816
 64     61                 2         1       79161      10967
 65     62                 2         1       12364       3909

Re: adding variables in proc summary

Tom — Thu, 20 Oct 2022 19:11:42 GMT

You probably just need a multilabel format.

data have;
  infile cards dsd truncover ;
  input cos total underpov region $;
cards;
1,8913,2910,ROS
2,15,2,ROS
58,120347,43223,NYC
3,2923,1423,ROS
4,317,106,ROS
5,338,148,ROS
6,681,428,ROS
7,950,185,ROS
8,58,37,ROS
9,212,56,ROS
10,269,17,ROS
11,220,7,ROS
12,59,15,ROS
13,6108,1195,ROS
14,29917,11954,ROS
15,21,13,ROS
16,71,60,ROS
17,147,147,ROS
18,129,53,ROS
19,128,98,ROS
20,0,0,ROS
21,276,47,ROS
22,1434,459,ROS
59,167871,43213,NYC
23,66,8,ROS
24,206,114,ROS
25,65,31,ROS
26,31252,14751,ROS
27,386,171,ROS
28,32295,2973,ROS
60,41041,15816,NYC
29,3887,1680,ROS
30,3584,2158,ROS
31,15001,8066,ROS
32,490,36,ROS
33,9528,1109,ROS
34,207,63,ROS
35,270,96,ROS
36,200,50,ROS
37,561,40,ROS
61,79161,10967,NYC
38,2347,958,ROS
62,12364,3909,NYC
39,9205,1688,ROS
40,47,15,ROS
41,747,193,ROS
42,4122,1594,ROS
43,50,0,ROS
44,13,5,ROS
45,137,28,ROS
46,353,190,ROS
47,25916,3601,ROS
48,1279,631,ROS
49,53,21,ROS
50,615,288,ROS
51,1744,665,ROS
52,84,15,ROS
53,27,1,ROS
54,432,167,ROS
55,28775,4849,ROS
56,36,15,ROS
57,4,4,ROS
;
data formats;
  set have end=eof;
  fmtname='REGION';
  hlo='SM';
  start = cos;
  label = region;
  output;
  label = 'NYS';
  output;
  label = 'C' || put(cos,z2.);
  output;
  keep fmtname start label hlo ;
run;

proc sort;
  by start label;
run;

proc format cntlin=formats;
run;

proc summary data=have nway;
  class cos / mlf ;
  format cos region. ;
  var total underpov ;
  output out=want sum= ;
run;

proc print;
run;

Re: adding variables in proc summary

geneshackman — Thu, 20 Oct 2022 19:20:22 GMT

Just to mention, I have 999 and 102 because you can get State and NYC from the ACS, so I did. You can't get ROS from ACS.