topic Re: How to add a row with value 0? in SAS Programming

How to add a row with value 0?

Crubal — Fri, 17 Mar 2017 16:14:58 GMT

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!

Re: How to add a row with value 0?

collinelliot — Fri, 17 Mar 2017 16:31:48 GMT

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;

Re: How to add a row with value 0?

Crubal — Fri, 17 Mar 2017 17:47:25 GMT

Hi,

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

Re: How to add a row with value 0?

Reeza — Fri, 17 Mar 2017 17:54:01 GMT

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.

Re: How to add a row with value 0?

data_null__ — Fri, 17 Mar 2017 18:01:33 GMT

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;

Re: How to add a row with value 0?

ballardw — Fri, 17 Mar 2017 19:38:33 GMT

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;