topic Re: how the calculate the total of every using first and last temporary variable ? in SAS Programming

how the calculate the total of every using first and last temporary variable ?

tianerhu — Fri, 21 May 2021 16:11:47 GMT

Obs	AIR	date
1	112	1949
2	118	1949
3	132	1949
4	129	1949
5	121	1949
6	135	1949
7	148	1949
8	148	1949
9	136	1949
10	119	1949
11	104	1949
12	118	1949
13	115	1950
14	126	1950
15	141	1950
16	135	1950
17	125	1950
18	149	1950
19	170	1950
20	170	1950
21	158	1950
22	133	1950
23	114	1950
24	140	1950
25	145	1951
26	150	1951
27	178	1951
28	163	1951
29	172	1951
30	178	1951
31	199	1951
32	199	1951
33	184	1951
34	162	1951
35	146	1951
36	166	1951
37	171	1952
38	180	1952
39	193	1952
40	181	1952
41	183	1952
42	218	1952
43	230	1952
44	242	1952
45	209	1952
46	191	1952
47	172	1952
48	194	1952
49	196	1953
50	196	1953
51	236	1953
52	235	1953
53	229	1953
54	243	1953
55	264	1953
56	272	1953
57	237	1953
58	211	1953
59	180	1953
60	201	1953
61	204	1954
62	188	1954
63	235	1954
64	227	1954
65	234	1954
66	264	1954
67	302	1954
68	293	1954
69	259	1954
70	229	1954
71	203	1954
72	229	1954
73	242	1955
74	233	1955
75	267	1955
76	269	1955
77	270	1955
78	315	1955
79	364	1955
80	347	1955
81	312	1955
82	274	1955
83	237	1955
84	278	1955
85	284	1956
86	277	1956
87	317	1956
88	313	1956
89	318	1956
90	374	1956
91	413	1956
92	405	1956
93	355	1956
94	306	1956
95	271	1956
96	306	1956
97	315	1957
98	301	1957
99	356	1957
100	348	1957
101	355	1957
102	422	1957
103	465	1957
104	467	1957
105	404	1957
106	347	1957
107	305	1957
108	336	1957
109	340	1958
110	318	1958
111	362	1958
112	348	1958
113	363	1958
114	435	1958
115	491	1958
116	505	1958
117	404	1958
118	359	1958
119	310	1958
120	337	1958
121	360	1959
122	342	1959
123	406	1959
124	396	1959
125	420	1959
126	472	1959
127	548	1959
128	559	1959
129	463	1959
130	407	1959
131	362	1959
132	405	1959
133	417	1960
134	391	1960
135	419	1960
136	461	1960
137	472	1960
138	535	1960
139	622	1960
140	606	1960
141	508	1960
142	461	1960
143	390	1960
144	432	1960

Re: how the calculate the total of every using first and last temporary variable ?

PGStats — Fri, 21 May 2021 16:55:53 GMT

Use the first. variable to reset your total to zero (if first.year then total=0;) and the last. variable to write the output (if last.year then output;). Don't forget to update total at every obs (total = total + AIR;) and to retain the value of total (retain total;) beween obs.

Re: how the calculate the total of every using first and last temporary variable ?

ballardw — Fri, 21 May 2021 17:13:03 GMT

How about showing fewer records and then what you expect for the result.

You don't say "first" or "last" of what. Or "total of every" what.

Re: how the calculate the total of every using first and last temporary variable ?

Cynthia_sas — Fri, 21 May 2021 18:27:25 GMT

Hi:

Your data looks almost exactly like SASHELP.AIR, except where they have a date formatted as monyy5, you have a year value labeled as date. So, making only 5 years of data, as shown below:


** making only 5 years of data;
data work.air;
  infile datalines dlm=',';
  input air date;
return;
datalines;
112,1949
118,1949
132,1949
129,1949
121,1949
135,1949
148,1949
148,1949
136,1949
119,1949
104,1949
118,1949
115,1950
126,1950
141,1950
135,1950
125,1950
149,1950
170,1950
170,1950
158,1950
133,1950
114,1950
140,1950
145,1951
150,1951
178,1951
163,1951
172,1951
178,1951
199,1951
199,1951
184,1951
162,1951
146,1951
166,1951
171,1952
180,1952
193,1952
181,1952
183,1952
218,1952
230,1952
242,1952
209,1952
191,1952
172,1952
194,1952
196,1953
196,1953
236,1953
235,1953
229,1953
243,1953
264,1953
272,1953
237,1953
211,1953
180,1953
201,1953
;
run;
  
proc sort data=work.air; by date;
run;

Then using first. and last. variables and 2 cumulative (summarized) variables, you can generate this #1 report using the data set created in the DATA step program. I also included 2 separate steps for PROC REPORT and PROC TABULATE that generate the numbers you want without using a DATA step program:

My tendency given what you described would be to use either PROC REPORT or PROC TABULATE without doing any extra processing or creating a new dataset.

Cynthia

Re: how the calculate the total of every using first and last temporary variable ?

tianerhu — Fri, 21 May 2021 18:37:23 GMT

This is what I need . Thank you so much.

Re: how the calculate the total of every using first and last temporary variable ?

tianerhu — Fri, 21 May 2021 18:38:46 GMT

Thank you for your reply.

Re: how the calculate the total of every using first and last temporary variable ?

tianerhu — Fri, 21 May 2021 21:05:01 GMT

Thank you for your help.

Re: how the calculate the total of every using first and last temporary variable ?

PaigeMiller — Fri, 21 May 2021 21:26:45 GMT

May I suggest, @tianerhu, that you don't try to write your own code to create sums (or other statistics) in a group? Although it certainly is possible, as shown by the people in this thread, it's much simpler to use PROC SUMMARY, and it has the benefit that SAS has debugged and tested the code and the results have been verified in many bazillions of real-world applications.

proc summary nway data=have;
    class date;
    var air;
    output out=sums sum=air_sum;
run;

PROC SUMMARY has lots of features above and beyond calculating group sums, and you would be wise to learn that, instead of writing DATA step code to do so.

Re: how the calculate the total of every using first and last temporary variable ?

tianerhu — Fri, 21 May 2021 23:46:10 GMT

Thank you for your help.

Re: how the calculate the total of every using first and last temporary variable ?

Cynthia_sas — Sat, 22 May 2021 00:44:49 GMT

Thanks @PaigeMiller. I would have used REPORT or TABULATE, but you're right, MEANS/SUMMARY is also a viable alternative. The reason I like REPORT and TABULATE is that they create a summarized report without creating a summarized data set. But if you NEED a summarized dataset, then PROC MEANS/PROC SUMMARY would be the way to do it!
Cynthia

Re: how the calculate the total of every using first and last temporary variable ?

PaigeMiller — Sat, 22 May 2021 09:27:05 GMT

Yes, @Cynthia_sas, I agree, tables or reports are best done in PROC TABULATE or PROC REPORT. SAS data sets for further analysis should be PROC MEANS or PROC SUMMARY.

Re: how the calculate the total of every using first and last temporary variable ?

PaigeMiller — Sat, 22 May 2021 09:36:26 GMT

@tianerhu wrote:

Thank you for your help.

For your future benefit, I have annotated my code to help you get up to speed with PROC SUMMARY.

proc summary nway data=have;
    class date; /* Group variable(s) goes in the CLASS statement */
    var air; /* Variable(s) to be summed, or to have any other statistic computed */
        /* go in the VAR statement */
    output out=sums sum=air_sum; /* OUT= gives the name of the output data set */
    /* SUM= gives the name of the output variable containing the SUM */
    /* You could optionally add in MEAN=air_mean STDDEV=air_stddev to compute means and standard deviations by group */
run;

Re: how the calculate the total of every using first and last temporary variable ?

tianerhu — Sun, 23 May 2021 18:01:45 GMT

Thank you for your help.