frequency and total

[Prashant_Ph]

Hi,

I have a Data Set which have a 4 years of sales  data, i want to know only the total sales in the year 2010 and freq of 2010 sales by using proc step. i have attached XL file pls find it. 

Thank You

[Reeza]

Please include what you've tried. 

What part are you having difficulty with?

What at do you mean by proc step? 

Also, a lot of users don't download files so consider including a sample of data in your post directly. 

[PGStats]

@Lithium-Admin, why can't I see the xlsx attachment to this message on Chrome? I get the message Error displaying attachment content.

[Reeza]

---

@PGStats wrote:

@Lithium-Admin, why can't I see the xlsx attachment to this message on Chrome? I get the message Error displaying attachment content.

---

I get that when it's not a true XLSX. I think...

[Astounding]

This would be a typical approach:

proc means data=sales n sum;

var sales_amount;

where year=2010;

run;

You might have to adjust slightly, using the proper names for your variables and data set.

[RW9]

Too get good answers, post test data in the form of a datastep using the insert code icon - {i} - above your post.  Excel files are dangerous and I would not download them.  Also providing an example of what you require will get you better results.  As a guess you could use proc sql:

proc sql; 
  create table WANT as
  select  YEAR, 
              sum(SALES) as TOTAL,
              count(SALES) as FREQ
  from    (select * from HAVE where YEAR=2010)
  group by YEAR;
quit; 

[Prashant_Ph]

Hi All,

This question was asked in interview to me, question is simple, interviewer asked me there is a data set, and dataset have a 4 years of sales data Exp:- 2010, 11, 12, and 13 sales data. just for a example data have 2 variable that is Date and sales, and he asked me to right a syntax to find out only 2010 total sales and total sales frequency. so can i get a solutation from any one? how to find out the answer by useing Data step and Proc step's .? I have attached XLS file. Pls have a look on attached file and replay to me.

Thank You

proc freq data = sales123;                                                                                                              
table Order_Date sales;                                                                                                                 
format Order_Date  year4.;                                                                                                              
run;

proc freq data = sales123;                                                                                                              
table Order_Date sales;                                                                                                                 
format Order_Date  year4.;                                                                                                              
run;  

by using proc freq am not getting exat answer which what i want.

80   proc print data = sales123;
81   var Order_Date sales;
82   where Order_Date = 2010;
83   run;

NOTE: No observations were selected from data set WORK.SALES123.
NOTE: There were 0 observations read from the data set WORK.SALES123.
      WHERE Order_Date=2010;
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.15 seconds
      cpu time            0.01 seconds

[RW9]

Hi,

Sorry as stated above, Excel files are not a good medium, they are unsafe.  You have been provided seveal examples above.  Your where clause probably (and as I can't see the data I can't say) fails as it is a date.  Dates are numbers which reflect number of days since a certain timepoint, to compare this to a year value you would need to use year() function:

proc print data = sales123;
  var order_date sales;
  where year(order_date)=2010;
run;

You should really read the manual and SAS training on how to handle dates/times to understand why your where didn't work.

Note, it is the year() function.

[Prashant_Ph]

See a sample of the data below - it's long.

Spoiler

Order_Date			Sales
01-01-09			195.95
01-01-09			898.83
02-01-09			125.87
02-01-09			147.82
02-01-09			648.61
02-01-09			1215.72
02-01-09			4387.07
02-01-09			4968.94
03-01-09			164.78
03-01-09			902.51
03-01-09			84.83
03-01-09			890.41
03-01-09			535.27
03-01-09			28186.7
03-01-09			123.91
03-01-09			868.5
03-01-09			124.71
03-01-09			272.91
04-01-09			63.36
04-01-09			157.68
04-01-09			1082.88
05-01-09			752.04
05-01-09			78.5
05-01-09			661.39
05-01-09			13449.97
05-01-09			246.19
05-01-09			3154.97
05-01-09			4091.83
05-01-09			8624.63
05-01-09			4817.7
05-01-09			261.74
05-01-09			718.3
05-01-09			189.14
05-01-09			172.84
05-01-09			2271.87
05-01-09			1263.14
06-01-09			36.37
06-01-09			47.83
06-01-09			1058.4
06-01-09			306.72
06-01-09			156.99
06-01-09			2358.36
06-01-09			26473.91
06-01-09			13.77
06-01-09			131.06
06-01-09			3502.53
06-01-09			1314.86
06-01-09			73.05
06-01-09			99.06
07-01-09			11.25
07-01-09			638.14
07-01-09			162.45
07-01-09			731.81
07-01-09			10.66
07-01-09			2124.39
07-01-09			47505.56
07-01-09			748.64
07-01-09			2249.6
08-01-09			67
08-01-09			77.12
08-01-09			338.31
08-01-09			22444.71
09-01-09			67.6
09-01-09			134.94
09-01-09			44.04
09-01-09			124.5
09-01-09			129.59
09-01-09			66.74
09-01-09			148.24
09-01-09			510.37
10-01-09			2247.07
10-01-09			556.71
10-01-09			155.74
10-01-09			201.11
10-01-09			66.23
10-01-09			1157.29
10-01-09			1316.66
10-01-09			504.63
10-01-09			1029.83
10-01-09			14599.03
10-01-09			5233.66
10-01-09			112.83
11-01-09			5967.42
11-01-09			585.91
11-01-09			266.25
11-01-09			1218.37
11-01-09			7611.6
11-01-09			126.61
11-01-09			209.1
12-01-09			1623.44
12-01-09			416.2
12-01-09			1129.9
12-01-09			15824.07
12-01-09			56.4
12-01-09			4250.83
12-01-09			246.11
12-01-09			87.8
13-01-09			304.72
13-01-09			40.63
13-01-09			454.17
13-01-09			629.59
14-01-09			218.94
14-01-09			7067.73
14-01-09			256.05
14-01-09			2982.16
14-01-09			743.76
14-01-09			35.72
14-01-09			60.84
14-01-09			590.82
14-01-09			9557.14
14-01-09			7441.43
14-01-09			2491.85
14-01-09			116.42
15-01-09			208.92
15-01-09			299.43
15-01-09			51.39
15-01-09			26.53
15-01-09			210.13
15-01-09			418.99
16-01-09			275.65
16-01-09			74.93
16-01-09			61.59
16-01-09			3158.73
16-01-09			849.59
16-01-09			475.55
16-01-09			679.85
16-01-09			243.16
16-01-09			79.17
16-01-09			387.74
17-01-09			545.77
17-01-09			1241.11
17-01-09			78.84
17-01-09			291.01
17-01-09			7966.36
17-01-09			291.4
17-01-09			601.96
17-01-09			138.81
17-01-09			380.47
17-01-09			205.8
17-01-09			48.41
17-01-09			1103.97
18-01-09			741.07
18-01-09			8813.7
18-01-09			329.16
18-01-09			2906.44
18-01-09			144.66
18-01-09			1424.14
18-01-09			281.43
19-01-09			103.78
19-01-09			3735.54
19-01-09			2215.96
19-01-09			118.69
20-01-09			3859.92
20-01-09			2171.4
21-01-09			4500.11
21-01-09			177.24
21-01-09			98.75
21-01-09			334.47
21-01-09			1220.58
21-01-09			143.93
21-01-09			143.11
21-01-09			1401.71
21-01-09			34.81
21-01-09			326.07
21-01-09			4238.06
21-01-09			11806.22
21-01-09			388.21
21-01-09			807.81
22-01-09			28235.89
22-01-09			4879.65
22-01-09			4097.71
22-01-09			2905.64
22-01-09			1087.73
22-01-09			557.63
22-01-09			203.29
22-01-09			142.04
22-01-09			8525.25
22-01-09			196.91
23-01-09			1306.85
23-01-09			131.49
23-01-09			1343.77
24-01-09			7176.39
24-01-09			4224.25
24-01-09			276.05
24-01-09			340.11
24-01-09			352.82
25-01-09			1898.74
25-01-09			10.98
26-01-09			4514.14
26-01-09			71.06
26-01-09			4694.29
26-01-09			404.03
26-01-09			1301.62
26-01-09			406.83
26-01-09			9112.69
26-01-09			435.17
26-01-09			14.94
27-01-09			106.45
27-01-09			1812.14
27-01-09			57.64
27-01-09			7830.04
27-01-09			446.44
28-01-09			249.68
28-01-09			1041.85
28-01-09			183.91
28-01-09			181.83
28-01-09			69.72
28-01-09			6.59
29-01-09			1348.67
29-01-09			16017.21
29-01-09			1024.1
30-01-09			17841.36
30-01-09			376.45
30-01-09			1336.65
31-01-09			51.62
31-01-09			8819.6
31-01-09			485.79
31-01-09			136.05
31-01-09			2319.43
31-01-09			252.88
31-01-09			6791.07
31-01-09			258.71
31-01-09			150.2
01-02-09			52.85
01-02-09			1673.96
01-02-09			3353.31
02-02-09			3163.89
02-02-09			3877.29
02-02-09			363.92
02-02-09			4846.93
02-02-09			6096.62
02-02-09			44
03-02-09			186.41
03-02-09			940.72
04-02-09			1496.42
04-02-09			171.59
04-02-09			69.92
04-02-09			118.17
04-02-09			212.47
04-02-09			226.36
04-02-09			171.3
04-02-09			73.63
04-02-09			75.12
04-02-09			233.37
04-02-09			6360.27
04-02-09			4817.62
04-02-09			630.04
04-02-09			193.42
04-02-09			686.95
04-02-09			6.81
05-02-09			212.89
05-02-09			139.18
05-02-09			1179.83
05-02-09			6458.76
05-02-09			162.14
05-02-09			19566.29
05-02-09			555.3
06-02-09			7274.07
06-02-09			1587.72
06-02-09			7857.95
06-02-09			1210.9
06-02-09			832.92
06-02-09			2013.88
07-02-09			312.89
07-02-09			257.24
07-02-09			1408.68
07-02-09			608.04
07-02-09			3535.33
08-02-09			739.88
08-02-09			591.14
08-02-09			2133.49
08-02-09			759.98
09-02-09			10422.33
10-02-09			469.91
10-02-09			145.27
10-02-09			105.43
10-02-09			906.71
10-02-09			63.91
11-02-09			1085.58
11-02-09			9896.46
11-02-09			3716.73
11-02-09			9805.21
11-02-09			14270.05
12-02-09			3335.51
12-02-09			2478.49
12-02-09			19.67
12-02-09			126.69
12-02-09			87.13
12-02-09			110.04
12-02-09			135.48
12-02-09			192.7
12-02-09			67.1
12-02-09			4410.07
12-02-09			210.54
12-02-09			247.9
13-02-09			3673.66
13-02-09			48.93
13-02-09			5381.74
13-02-09			354.81
13-02-09			8172.11
14-02-09			254.79
14-02-09			947.73
14-02-09			8116.22
14-02-09			2825.78
15-02-09			88.86
15-02-09			160.78
15-02-09			19275.56
15-02-09			36.26
15-02-09			65.85
15-02-09			135.61
15-02-09			4805.19
15-02-09			2695.35
16-02-09			324.35
16-02-09			10524.27
16-02-09			204.85
16-02-09			208.79
16-02-09			180.63
16-02-09			137.65
17-02-09			237.93
17-02-09			632.6
17-02-09			946.51
17-02-09			9704.05
17-02-09			14.17
18-02-09			10738.23
18-02-09			1819.6
19-02-09			1897
19-02-09			222.22
19-02-09			10.86
19-02-09			178.86
19-02-09			377.55
19-02-09			163.48
20-02-09			246.63
20-02-09			479.03
20-02-09			157.08
20-02-09			180.49
20-02-09			344.1
20-02-09			467.62
21-02-09			919.79
21-02-09			191.89
21-02-09			2923.39
22-02-09			139.03
22-02-09			1442.66
22-02-09			17700.3
22-02-09			1757.15
23-02-09			243.2
23-02-09			1007.4
23-02-09			451.64
23-02-09			843.59
24-02-09			2383.37
24-02-09			14009.81
24-02-09			3.81
24-02-09			586.92
25-02-09			1146.53
25-02-09			2545.38
25-02-09			329.29
25-02-09			26.92
25-02-09			1143.7
26-02-09			139.5
26-02-09			838.91
26-02-09			4516.7
26-02-09			120.45
26-02-09			990.96
26-02-09			725.7
26-02-09			34.5
26-02-09			185.18
26-02-09			10024.26
27-02-09			74.28
27-02-09			11532.65
27-02-09			1364.48
27-02-09			341.68
27-02-09			2024.57
28-02-09			1433.75
28-02-09			2186.61
28-02-09			193.66
28-02-09			3657.81
01-03-09			335.24
01-03-09			3524.42
01-03-09			430.81
01-03-09			923.61
01-03-09			713.9
01-03-09			32.37
01-03-09			1752.55
02-03-09			11.57
02-03-09			1884.18
02-03-09			2644.82
02-03-09			895.07
02-03-09			393.5
02-03-09			206.79
03-03-09			75.97
04-03-09			12.35
04-03-09			3990.7
04-03-09			1244.83
05-03-09			441.44
05-03-09			92.51
05-03-09			2694.01
05-03-09			6273.82
05-03-09			182.41
05-03-09			191.07
05-03-09			188.48
05-03-09			482.95
05-03-09			2384.43
06-03-09			1634.41
06-03-09			3257.42
06-03-09			1169.74
06-03-09			110.49
07-03-09			10366.14
08-03-09			2443.22
08-03-09			2609.1
08-03-09			924.17
08-03-09			3375.5
08-03-09			296.68
09-03-09			813.77
09-03-09			1190.93
09-03-09			268.78
09-03-09			13280.2
09-03-09			117.22
10-03-09			619.92
10-03-09			186.72
10-03-09			579.18
10-03-09			20373.46
11-03-09			1119.82
11-03-09			3497.05
11-03-09			926.78
11-03-09			315.73
12-03-09			125.29
12-03-09			4702.46
12-03-09			1490.98
12-03-09			1483.5
12-03-09			1777.44
12-03-09			1409.59
12-03-09			1124.22
13-03-09			17.38
13-03-09			254.41
13-03-09			2519.71
13-03-09			130.43
14-03-09			2124.86
14-03-09			7258.97
14-03-09			1104.93
14-03-09			9100.04
14-03-09			107.87
14-03-09			1634.76
14-03-09			5925.85
15-03-09			260.88
15-03-09			309.56
15-03-09			7162.4
15-03-09			405.93
15-03-09			30.61
15-03-09			90.47
16-03-09			219.27
16-03-09			87.42
16-03-09			1436.12
16-03-09			372.41
17-03-09			85.55
17-03-09			311.38
17-03-09			238.47
18-03-09			5754.52
18-03-09			403.61
18-03-09			142.58
18-03-09			2530.78
18-03-09			54.97
18-03-09			9787.77
18-03-09			29.25
18-03-09			430.62
19-03-09			287
19-03-09			1139.13
19-03-09			155.09
19-03-09			1259
19-03-09			95.8
19-03-09			2466.73
19-03-09			362.47
20-03-09			143.85
20-03-09			9849.63
20-03-09			102.34
20-03-09			422.04
20-03-09			23.93
20-03-09			4864.36
21-03-09			88203.75
21-03-09			8248.51
21-03-09			17280.14
21-03-09			2467.89
21-03-09			503.25
21-03-09			200.66
22-03-09			7534.5
22-03-09			869.79
22-03-09			22.97
22-03-09			4672.24
22-03-09			337.88
22-03-09			640.33
22-03-09			7113.09
22-03-09			627.13
22-03-09			2443.58
22-03-09			35.65
22-03-09			673.08
22-03-09			44.69
23-03-09			511.15
23-03-09			115.15
23-03-09			445.79
23-03-09			97.46
23-03-09			427.12
23-03-09			1824.4
23-03-09			89.55
24-03-09			167.73
24-03-09			1377.14
24-03-09			97.91
24-03-09			602.65
24-03-09			64.09
24-03-09			6201.4
24-03-09			3243.58
25-03-09			192.27
25-03-09			63.71
25-03-09			1674.73
25-03-09			578.12
25-03-09			287.27
25-03-09			1617.22
25-03-09			217.95
25-03-09			13567.88
25-03-09			114.92
25-03-09			57.16
26-03-09			4512.52
26-03-09			232.04
26-03-09			275.3
26-03-09			541.62
26-03-09			617.24
26-03-09			41.9
27-03-09			335.69
27-03-09			2549.25
27-03-09			14.85
27-03-09			6872.5
27-03-09			324.14
27-03-09			1719.98
27-03-09			551.21
27-03-09			9833.32
27-03-09			1749.16
27-03-09			56.52
27-03-09			134.36
27-03-09			304.16
27-03-09			743.24
27-03-09			93.66
27-03-09			547.22
28-03-09			923.67
28-03-09			2429.63
28-03-09			504.56
28-03-09			210.15
28-03-09			7044.51
28-03-09			287.44
28-03-09			170.23
29-03-09			221.19
29-03-09			687.06
29-03-09			175.21
29-03-09			1112.92
30-03-09			3754.58
30-03-09			27.68
30-03-09			246.07
30-03-09			923.53
30-03-09			177.53
30-03-09			243.25
30-03-09			284.89
31-03-09			60.92
31-03-09			31.18
31-03-09			172.64
01-04-09			1080.71
01-04-09			884.42
01-04-09			17.39
01-04-09			271.6
01-04-09			1521.63
01-04-09			2319.69
01-04-09			578.76
02-04-09			14298.04
02-04-09			649.96
02-04-09			1444.79
02-04-09			532.67
02-04-09			1737.3
03-04-09			1814.06
04-04-09			1981.78
04-04-09			1656.53
04-04-09			70.55
04-04-09			324.95
04-04-09			231.26
04-04-09			77.21
05-04-09			596.88
05-04-09			128.43
05-04-09			1290.79
05-04-09			1658.54
06-04-09			648.5
06-04-09			64.57
06-04-09			2377.52
06-04-09			648.83
06-04-09			78.1
06-04-09			368.1
06-04-09			1562.05
06-04-09			663.47
06-04-09			5551.24
06-04-09			9385.26
06-04-09			6533.12
06-04-09			242.44
06-04-09			767.74
06-04-09			313.4
07-04-09			1477.17
07-04-09			256.31
07-04-09			251.02
07-04-09			555.38
07-04-09			2380.9
07-04-09			66.66
07-04-09			877.64
08-04-09			1886.55
08-04-09			1465.93
08-04-09			19.34
08-04-09			1387.2
08-04-09			124.33
08-04-09			169.76
09-04-09			4996.51
09-04-09			472.38
09-04-09			81.79
09-04-09			84.81
09-04-09			14429.53
09-04-09			1071.89
09-04-09			10193.96
09-04-09			1015.23
09-04-09			357.72
09-04-09			77.62
09-04-09			15686.02
09-04-09			99.44
09-04-09			19.99
09-04-09			3787.32
10-04-09			392.98
10-04-09			788.37
10-04-09			4003.43
10-04-09			9607.06
10-04-09			207.63
10-04-09			54.12
10-04-09			1610.03
10-04-09			20.32
10-04-09			233.92
10-04-09			123.18
10-04-09			8062.11
11-04-09			430.55
11-04-09			987.45
11-04-09			356.24
11-04-09			1347.6
12-04-09			290.34
12-04-09			202.7
12-04-09			282.27
12-04-09			5129.9
13-04-09			953.41
13-04-09			25.63
13-04-09			3.32
14-04-09			1449.47
14-04-09			5385.3
14-04-09			964.29
15-04-09			944.16
15-04-09			4561.46
15-04-09			3480.94
15-04-09			809.59
15-04-09			439.56
16-04-09			15330.49
16-04-09			4670.61
16-04-09			104.35
16-04-09			15330.44
17-04-09			17206.76
17-04-09			260.96
17-04-09			1402.44
17-04-09			265.03
18-04-09			2291.66
18-04-09			5619.48
18-04-09			3042.52
18-04-09			4220.66
18-04-09			1598.58
18-04-09			1503.61
18-04-09			865.15
18-04-09			1720.76
19-04-09			18955.18
19-04-09			137.61
19-04-09			715.77
19-04-09			2433.79
19-04-09			4024.2
19-04-09			2294.84
19-04-09			5112.71
20-04-09			3570.14
20-04-09			240.8
20-04-09			131.62
20-04-09			122.01
20-04-09			265.41
20-04-09			11234.14
21-04-09			47.54
21-04-09			380.89
21-04-09			859.57
21-04-09			22.45
21-04-09			7125.96
21-04-09			7648.19
21-04-09			163.33
21-04-09			1083.16
22-04-09			158.35
22-04-09			951.88
22-04-09			288.2
22-04-09			3339.47
22-04-09			211.05
22-04-09			422.98
23-04-09			6.19
23-04-09			2349.58
23-04-09			2714.66
23-04-09			10527.5
23-04-09			31.98
23-04-09			29.22
24-04-09			1347.54
24-04-09			6448.69
24-04-09			109.48
24-04-09			192.74
24-04-09			137.95
24-04-09			3.58
25-04-09			122.38
26-04-09			3283.81
26-04-09			39.4
26-04-09			115.7
26-04-09			8403.75
27-04-09			2648.56
27-04-09			164.41
27-04-09			11443.95
27-04-09			582.4
27-04-09			566.83
28-04-09			77.45
28-04-09			116.59
28-04-09			2566.32
28-04-09			252.82
28-04-09			5400.09
28-04-09			107.63
29-04-09			2935.57
29-04-09			3535.33
29-04-09			7621.27
29-04-09			1117.77
30-04-09			2252.4
30-04-09			4707.96
30-04-09			947.99
01-05-09			843.06
01-05-09			508.72
01-05-09			110.66
01-05-09			146.82
01-05-09			324.88
01-05-09			78.13
01-05-09			655.29
02-05-09			183.46
02-05-09			2396.29
02-05-09			1287.21
02-05-09			157.22
02-05-09			165.46
03-05-09			1537.44

EDIT (REEZA): Data has been truncated and only has 2009 now, original data did include more years. 

[Prashant_Ph]

am sorry Data set have total 30 variables which i cant attach hear. so i copied and pasted only 2 variables which required to find-out the salutation.

[Reeza]

---

@Prashant_Ph wrote:

Hi All,

This question was asked in interview to me, question is simple, interviewer asked me there is a data set, and dataset have a 4 years of sales data Exp:- 2010, 11, 12, and 13 sales data. just for a example data have 2 variable that is Date and sales, and he asked me to right a syntax to find out only 2010 total sales and total sales frequency. so can i get a solutation from any one? how to find out the answer by useing Data step and Proc step's .? I have attached XLS file. Pls have a look on attached file and replay to me.

If I was interviewing and the candidate claimed to know SAS but couldn't answer that question I wouldn't believe them. 

[Prashant_Ph]

Still i have not got any perfect answer for my question.

[Reeza · Accepted solution]

proc means data=have;
where year(date)=2010;
var sales;
output out=want1 n=Count sum=Total;
run;

proc sql;
create table want2 as
select year(date) as year, sum(sales) as Total, count(*) as Count
from have
where year(date)=2010;
quit;

Both of these solutions were already proposed.

Use the YEAR() function to extract the year from the date and get the data for 2010.

Proc Means will generate both a total and count.

Proc SQL will also generate both a total and count. 

If this does not work please clearly explain what your issue is.

[Prashant_Ph]

Thank You @Reeza @RW9 i rally appreciate you ppl took a time and answered. frequently i started using SAS Communities and i don’t know how to attach data. but well answered by you people.

[RW9]

Hi,

To note, we do have jobs which take precedence over answering questions.

Firstly, when I ask to provide some test data, i mean 3-4 rows of data in a datastep.  This is because we cannot see your data - not just the contents, but the structure as well.  The logic to do this with a character field is very different to the logic used on a date column for instance.  Here is an example of how to post test data:

data have;
  informat order_date ddmmyy8.;
  input Order_Date Sales;
  format order_date date9.;
datalines;
01-01-09	195.95
01-01-09	898.83
02-01-09	125.87
02-01-09	147.82
;
run;

Now lets combine this with the year() function I provided earlier on and the example where clause:

data have;
  informat order_date ddmmyy8.;
  input Order_Date Sales;
  format order_date date9.;
  yr=year(order_date);
datalines;
01-01-09	195.95
01-01-09	898.83
02-01-09	125.87
02-01-09	147.82
;
run;

proc freq data=have;                                                                                                              
  table yr sales;                                                                                                                 
  where yr=2009;
run;

Now you see in the first datastep I create a yr variable which holds the year part of the date, this both simplfies later processing and gives a variable ready for tabling.  The where in the proc freq restricts the data only to  year 2009.