DATA Step, Macro, Functions and more

How to add a row with value 0?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 118
Accepted Solution

How to add a row with value 0?

Hi, 

 

I would like to obtain a dataset which has 3 columns: 'Stay_Date' / 'Prod_Class_ID' / 'Count'. 

 

'Stay_Date' is 2015-03-02 through 2017-03-01; Prod_Class-ID is 1 through 3 for every Stay_Date; Count is an integer value. 

 

In Table1, I obtained this one;

 

Stay_Date      |     Prod_Class_ID      |         Count 

2015-03-02    |    2                             |           4
2015-03-02    |    3                             |          10
2015-03-03    |    2                             |           5
2015-03-03    |    3                             |          11
2015-03-04    |    1                             |           1
2015-03-04    |    2                             |           9
2015-03-04    |    3                             |          12

.......                |    ....                           |           ...    

2017-03-01    |   1                              |           6

2017-03-01    |   2                              |           2

2017-03-01    |   3                              |           5

             

The following dataset has missing value for (a) '2015-03-02' & Prod_Class_ID = 1    and (b) '2015-03-03' & Prod_Class_ID = 1

I know the Count for these two combinatios are 0. How can I make them also show up in the table?

 

My idea is to have the following code that all 'Stay_Date' s with 'Prod_Class_ID' s are 0: 

 

Data WORK.Table2;


   do STAY_DATE = '02MAR2015'd to '01MAR2017'd;
       do PROD_CLASS_ID = 1 to 3;
           Count = 0;
           output;
       end;
   end;
FORMAT STAY_DATE yymmdd10.;
Run;

 

Output:

 

Stay_Date      |     Prod_Class_ID      |         Count 

2015-03-02    |             1                    |           0
2015-03-02    |             2                    |           0
2015-03-02    |             3                    |           0
2015-03-03    |             1                    |           0
2015-03-03    |             2                    |           0
2015-03-03    |             3                    |           0

.........                          .........                          ......

 

By Combining Table1 and Table2 using some Proc Sql procedures or Data Merge procedures and smething else, I obtained Table1 again. Can I ask how to write the code to make 'Count = 0' show up together with Table1 for the other dates & Prod_ClassID combinations? (Like Table3) 

 

Table3:

Stay_Date      |     Prod_Class_ID      |         Count 

2015-03-02    |    1                             |           0

2015-03-02    |    2                             |           4
2015-03-02    |    3                             |          10

2015-03-03    |    1                             |           0
2015-03-03    |    2                             |           5
2015-03-03    |    3                             |          11
2015-03-04    |    1                             |           1
2015-03-04    |    2                             |           9
2015-03-04    |    3                             |          12

.......                |    ....                           |           ...    

2017-03-01    |   1                              |           6

2017-03-01    |   2                              |           2

2017-03-01    |   3                              |           5

 

Thank you!


Accepted Solutions
Solution
‎03-17-2017 04:17 PM
Respected Advisor
Posts: 3,799

Re: How to add a row with value 0?

data stay;
   infile cards dsd dlm='|' firstobs=2;
   input Stay_Date :yymmdd. Prod_Class_ID Count;
   format stay_date yymmdd.;
   cards;
Stay_Date|Prod_Class_ID|Count
2015-03-02|2|4
2015-03-02|3|10
2015-03-03|2|5
2015-03-03|3|11
2015-03-04|1|1
2015-03-04|2|9
2015-03-04|3|12
2017-03-01|1|6
2017-03-01|2|2
2017-03-01|3|5
;;;;
   run;
proc print;
   run;
proc summary data=stay nway;
   class prod:;
   output out=classdata(drop=_:);
   run;
proc print;
   run;
proc summary data=stay classdata=classdata nway;
   by stay:;
   class prod:;
   freq count;
   output out=filled(rename=(_freq_=Count) drop=_type_);
   run;
proc print;
   run;

Capture.PNG

View solution in original post


All Replies
PROC Star
Posts: 307

Re: How to add a row with value 0?

You have a number of options, but if your data are that symmetrical, your template idea is not bad at all. You'd then just need to merge on your existing data and use the coalesce function to populate missing values with zero. Something like this:

 

proc sql;
    CREATE TABLE want AS
    SELECT a.stay_date, a.prod_class,
                   COALESCE(b.count, 0) AS count
    FROM table2 AS a /* Your template */
    LEFT JOIN table1 AS b
    ON a.stay_date = b.stay_date AND
          a.prod_class = b.prod_class;
quit;
Frequent Contributor
Posts: 118

Re: How to add a row with value 0?

Posted in reply to collinelliot

Hi, 

 

Thank you and I tried that, it returned all Stay dates from '2015-03-02' to '2017-03-01' with Counts 0. 

Super User
Posts: 19,855

Re: How to add a row with value 0?

Search PRELOADFMT, either on here, Google or LexJansen.com

 

PRELOADFMT allows you to add in values that don't originate in your dataset. 

 

The other common solution is the one proposed by @collinelliot and if it didn't work post your exact code and log. I suspect you may have accidently gotten a variable name wrong or something since it should work. 

Solution
‎03-17-2017 04:17 PM
Respected Advisor
Posts: 3,799

Re: How to add a row with value 0?

data stay;
   infile cards dsd dlm='|' firstobs=2;
   input Stay_Date :yymmdd. Prod_Class_ID Count;
   format stay_date yymmdd.;
   cards;
Stay_Date|Prod_Class_ID|Count
2015-03-02|2|4
2015-03-02|3|10
2015-03-03|2|5
2015-03-03|3|11
2015-03-04|1|1
2015-03-04|2|9
2015-03-04|3|12
2017-03-01|1|6
2017-03-01|2|2
2017-03-01|3|5
;;;;
   run;
proc print;
   run;
proc summary data=stay nway;
   class prod:;
   output out=classdata(drop=_:);
   run;
proc print;
   run;
proc summary data=stay classdata=classdata nway;
   by stay:;
   class prod:;
   freq count;
   output out=filled(rename=(_freq_=Count) drop=_type_);
   run;
proc print;
   run;

Capture.PNG

Super User
Posts: 11,343

Re: How to add a row with value 0?

An example with PRELOADFMT

data stay;
   infile cards dsd dlm='|' firstobs=2;
   input Stay_Date :yymmdd. Prod_Class_ID Count;
   format stay_date yymmdd10.;
   cards;
Stay_Date|Prod_Class_ID|Count
2015-03-02|2|4
2015-03-02|3|10
2015-03-03|2|5
2015-03-03|3|11
2015-03-04|1|1
2015-03-04|2|9
2015-03-04|3|12
2017-03-01|1|6
2017-03-01|2|2
2017-03-01|3|5
;;;;
   run;
proc format library=work;
value prod
1='1'
2='2'
3='3'
;
run;
proc summary data=stay nway completetypes;
   class Stay_Date;
   class prod: /preloadfmt ;
   format prod: prod.;
   freq count;
   output out=classdata(rename=(_freq_=Count) drop=_type_);
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 187 views
  • 0 likes
  • 5 in conversation