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 |
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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.