topic Re: Output Rows Even When No Value in SAS Programming

Output Rows Even When No Value

dolldata — Thu, 29 Apr 2021 18:49:17 GMT

I am sure this question has been asked and answered, but I've been searching for hours and am obviously not using the correct terms to find the answer.

How can I make sure a row is represented in output, even if there is no value? For example, when pulling orders by day, comparing two channels (website vs actual store). The website will have orders for every date as it never "closes". But, the stores will have some days missing due to closures for holiday. I am trying to keep the output consistent between channels. So, for example, in the store data, there would be a row for 4/4/2021 (Easter), but then the values of order or demand or whatever would all be blank.

Similarly, when I create my own format, I thought there was a way to output all values for that format, even if there is no data for it.

For example, in the below code, there are no data lines for April or September, but would want the output to still have a line for them:

proc format;
value months
	   1 = 'JAN'
	   2 = 'FEB'
	   3 = 'MAR'
	   4 = 'APR'
	   5 = 'MAY'
	   6 = 'JUN'
	   7 = 'JUL'
	   8 = 'AUG'
	   9 = 'SEP'
	   10 = 'OCT'
	   11 = 'NOV'
	   12 = 'DEC'
;
run;

data example;
input month orders;
datalines;
1 25
1 36
2 95
2 86
2 74
3 25
3 91
5 53
5 58
6 29
6 41
6 58
7 79
7 46
8 13
8 82
10 94
10 52
10 45
11 78
11 47
12 45
12 39
;
run;

proc summary data = example sum missing nway;
class month;
format month months.;
var orders;
output out = bymonth (drop = _type_ _freq_) sum = ;
run;

proc print data = bymonth noobs; run;

Output:

month	orders
JAN	61
FEB	255
MAR	116
MAY	111
JUN	128
JUL	125
AUG	95
OCT	191
NOV	125
DEC	84

Desired output:

month	orders
JAN	61
FEB	255
MAR	116
APR
MAY	111
JUN	128
JUL	125
AUG	95
SEP
OCT	191
NOV	125
DEC	84

Re: Output Rows Even When No Value

PeterClemmensen — Thu, 29 Apr 2021 18:58:59 GMT

Use the Completetypes Options and the PreloadFmt Option like this

proc format;
value months
	   1 = 'JAN'
	   2 = 'FEB'
	   3 = 'MAR'
	   4 = 'APR'
	   5 = 'MAY'
	   6 = 'JUN'
	   7 = 'JUL'
	   8 = 'AUG'
	   9 = 'SEP'
	   10 = 'OCT'
	   11 = 'NOV'
	   12 = 'DEC'
;
run;

data example;
input month orders;
datalines;
1 25
1 36
2 95
2 86
2 74
3 25
3 91
5 53
5 58
6 29
6 41
6 58
7 79
7 46
8 13
8 82
10 94
10 52
10 45
11 78
11 47
12 45
12 39
;
run;

proc summary data = example sum missing nway completetypes;
class month / preloadfmt;
format month months.;
var orders;
output out = bymonth (drop = _type_ _freq_) sum = ;
run;

Re: Output Rows Even When No Value

dolldata — Thu, 29 Apr 2021 23:18:15 GMT

Thank you - this is exactly what I needed for categories.

Is there something similar that can be done with date? Like I mentioned when outputting results by day, but there won't be records on holidays, but still want a line for them. Or do I need to create a new format for the entire date range I want to see?

Again, THANK YOU!