BookmarkSubscribeRSS Feed
dolldata
Obsidian | Level 7

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
2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

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;
dolldata
Obsidian | Level 7

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!

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 496 views
  • 3 likes
  • 2 in conversation